I am having trouble converting my 'have' data to my 'want' data type. I have tried a few tranpose functions but I just can't seem to get it working
have:
want:
DATE | missing | low | low medium | average | high | excellent |
2019-04 | 228221 | 3168 | 807 | 640 | 1880 | 1880 |
2019-05 | 3120 | 230281 | 2626 | 845 | 519 | 1931 |
proc transpose
data=have
out=want (drop=_name_)
;
by date;
id income;
var count;
run;
Post the log if it throws WARNINGs or ERRORs. You may have to change "low medium" to "low_medium" first to get a valid SAS name.
If this is for reporting purposes, use PROC REPORT and income as a ACROSS variable.
proc transpose
data=have
out=want (drop=_name_)
;
by date;
id income;
var count;
run;
Post the log if it throws WARNINGs or ERRORs. You may have to change "low medium" to "low_medium" first to get a valid SAS name.
If this is for reporting purposes, use PROC REPORT and income as a ACROSS variable.
Fantastic, thanks!
You need to search for the observations with missing income, and decide which value to set there.
missing values, show up as ".", is there a way I can include them in the transpose? since I want to keep them
So it looks that this is in fact a numeric variable with a custom format that does not cover missing values.
Change your preceding code so that it does not generate these missing values, or expand the format with a display value for missing.
73 proc format; 74 value income_fmt 75 . = "missing" 76 low -< 0 = "low" 77 1 -< 30 = "low-medium" 78 30 -< 60 = "average" 79 60 -< 90 = "high" 80 90 - high = "excellent"; 81 run;
Use the format to make a CHARACTER variable you can use to name the variables.
data step1 ;
set have;
name = put(income,income_fmt.);
run;
proc transpose data=step1 out=want ;
by date;
id name ;
var count;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.