transposing/changing format of data

[sasprogramming]

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

[Kurt_Bremser · Accepted solution]

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.

[sasprogramming]

Fantastic, thanks!

[sasprogramming]

WARNING: 25 observations omitted due to missing ID values.
How do I keep the missing row?

[Kurt_Bremser]

You need to search for the observations with missing income, and decide which value to set there.

[sasprogramming]

missing values, show up as ".", is there a way I can include them in the transpose? since I want to keep them

[Kurt_Bremser]

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.

[sasprogramming]

can't seem to get it working...

 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;

This creates the missing values, however when I tranpose my data it still loses these?

[Tom]

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;