topic Re: transposing/changing format of data in SAS Programming

transposing/changing format of data

sasprogramming — Sun, 23 May 2021 14:23:02 GMT

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

Re: transposing/changing format of data

Kurt_Bremser — Sun, 23 May 2021 14:28:33 GMT

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.

Re: transposing/changing format of data

sasprogramming — Sun, 23 May 2021 14:33:05 GMT

Fantastic, thanks!

Re: transposing/changing format of data

sasprogramming — Sun, 23 May 2021 14:47:50 GMT

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

Re: transposing/changing format of data

Kurt_Bremser — Sun, 23 May 2021 14:55:05 GMT

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

Re: transposing/changing format of data

sasprogramming — Sun, 23 May 2021 14:59:10 GMT

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

Re: transposing/changing format of data

Kurt_Bremser — Sun, 23 May 2021 15:05:18 GMT

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.

Re: transposing/changing format of data

sasprogramming — Sun, 23 May 2021 15:44:38 GMT

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?

Re: transposing/changing format of data

Tom — Mon, 24 May 2021 03:40:18 GMT

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;