topic Re: Conversion of a table in SAS in New SAS User

Conversion of a table in SAS

Tim_sas_ — Fri, 17 Jul 2020 19:59:37 GMT

Hello,

I have a very basic question regarding a table where I would like to change the order of rows and columns.

The problem is illustrated by the picture below. I have a SAS table in the upper format and would like to transform it to the format describes below.

I would be very glad if some could help me out.

Thanks a lot.

Peter

Re: Conversion of a table in SAS

Reeza — Fri, 17 Jul 2020 20:05:04 GMT

This is called a transpose.

Transposing data tutorials are here
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

Untested but this should be pretty close.

proc sort data=have;
by startYear variable;
run;

proc transpose data=have out=want;
by startYear;
ID variable;
value Estimate;
run;

@Tim_sas_ wrote:

Hello,

I have a very basic question regarding a table where I would like to change the order of rows and columns.

The problem is illustrated by the picture below. I have a SAS table in the upper format and would like to transform it to the format describes below.

I would be very glad if some could help me out.

Thanks a lot.

Peter

Re: Conversion of a table in SAS

PGStats — Fri, 17 Jul 2020 20:08:26 GMT

Try this:

proc sort data=myData; by startYear variable; run;

proc transpose data=myData out= want(drop=_name_);
by startYear;
var estimate;
id variable;
run;

(untested)

Re: Conversion of a table in SAS

ballardw — Fri, 17 Jul 2020 20:08:45 GMT

If all of the values of your Variable are acceptable as SAS variable names this should do it:

Assumes the data is sorted by startyear. If it isn't sorted then sort before the proc transpose.

Proc transpose data=have 
   out=want;
   by startyear;
   id variable;
   var estimate;
run;

This will however fail of any startyear value has multiple rows with the same value of Variable.

Re: Conversion of a table in SAS

Tim_sas_ — Fri, 17 Jul 2020 20:14:00 GMT

Thank you very much, this was very helpful!

Re: Conversion of a table in SAS

Tim_sas_ — Fri, 17 Jul 2020 20:14:41 GMT

Thanks a lot, your code worked excellently. Thank you.

Re: Conversion of a table in SAS

andreas_lds — Mon, 20 Jul 2020 08:04:44 GMT

Could be a job for proc report using across, if you don't need a dataset, but a report.