Joined: 28 Aug 2007 Posts: 1742 Location: Tirupur, India
Hello,
There is a SAS dataset which looks like this,
Code:
Obs DATE MEASURE STAT INDICATOR
1 01APR00 WEIGHT 64 A
2 01APR00 WEIGHT 93 B
3 01APR00 WEIGHT 50 D
4 01APR00 HEIGHT 169 A
5 01APR00 HEIGHT 170 B
6 01APR01 WEIGHT 54 A
7 01APR01 HEIGHT 170 A
I have to process this dataset and produce an output which looks like this using PROC REPORT,
Expected output:
Code:
HEIGHT WEIGHT
DATE INDICATOR STAT STAT
01APR00 A 169 64
01APR00 B 170 93
01APR00 D . 50
01APR01 A 170 54
I tried the below code,
Code:
PROC REPORT DATA = WELLS NOWD;
COLUMN DATE INDICATOR MEASURE,STAT;
DEFINE DATE / FORMAT=DATE. GROUP;
DEFINE INDICATOR / GROUP WIDTH=10;
DEFINE MEASURE / ACROSS;
DEFINE STAT / SUM;
QUIT;
Corresponding output:
Code:
MEASURE
HEIGHT WEIGHT
DATE INDICATOR STAT STAT
01APR00 A 169 64
B 170 93
D . 50
01APR01 A 170 54
But the output does not have DATE on each record of the output. Could you please let me know how to change the PROC REPORT so that the output will have DATE on each record.
The purpose to have date in each record is to do further Excel processing which requires date in each record.
I am not sure this will work or not, since we dont have sas in our shop. You can also try to do research on •
INCLUDEMISSINGCLASS=, I am eagerly waiting for a better solution for your post.[/url]
Joined: 28 Aug 2007 Posts: 1742 Location: Tirupur, India
Hi Suresh,
Thanks for your time with this post. But the link is only pertaining to processing of missing values in calculations FREQ procedure.
The requirement is to display all occurances of a group variable in PROC REPORT(with an across variable).
I could use proc transpose + proc print, but it takes 2 steps and needs more CPU. I am sure PROC REPRORT could do it in single step, but having trouble taming it.
Joined: 28 Aug 2007 Posts: 1742 Location: Tirupur, India
1000th post at last.. Hope some of them were helpful.
my crush came and talked to me today and now 1000th post.. day could not have been any better :-)
Hi Pumba,
I tried removing the GROUP parameter, but when I remove it, PROC REPORT groups the data based on INDICATOR column and considers the date as numerics and sums it.
Output after removing GROUP parameter,
Code:
measure
HEIGHT WEIGHT
date indicator stat stat
31DEC22 A 339 118
01JUL40 B 170 93
01APR00 D . 50
PROC REPORT DATA = WELLS NOWD OUT=THINK;
COLUMN DUMYDATE DATE INDICATOR MEASURE,STAT;
DEFINE DUMYDATE / COMPUTED 'DATE';
DEFINE DATE / group NOPRINT;
DEFINE INDICATOR / GROUP WIDTH=10;
DEFINE MEASURE / ACROSS;
DEFINE STAT / SUM;
COMPUTE BEFORE DATE;
STORED_DATE=PUT(DATE,DATE.);
ENDCOMP;
COMPUTE DUMYDATE / CHARACTER ;
DUMYDATE = STORED_DATE;
ENDCOMP;
QUIT;
produced the expected output :-)
Code:
measure
HEIGHT WEIGHT
DATE indicator stat stat
01APR00 A 169 64
01APR00 B 170 93
01APR00 D . 50
01APR01 A 170 54
Also a Respected SAS expert PM'ed me
Quote:
THE ODS TAGSETS.RTF STATEMENT
As mentioned previously, the new SPANROWS option repeats the values for the group and order variables when
there is a continuation of the same value on the next page for all ODS destinations.
But was unable to make it work though, probably since my tagsets were outdated.
PROC SQL;
CREATE TABLE HTAB AS
SELECT DATE, INDICATOR,
MAX(CASE WHEN MEASURE = 'HEIGHT' THEN STAT END)AS HEIGHT,
MAX(CASE WHEN MEASURE = 'WEIGHT' THEN STAT END)AS WEIGHT
FROM IP
GROUP BY DATE, INDICATOR
;
QUIT;
PROC PRINT DATA=HTAB;