topic Re: Transform multiple datasets from long to wide in SAS Programming

Transform multiple datasets from long to wide

luvscandy27 — Thu, 04 Aug 2022 21:19:44 GMT

Is there a way to transform multiple datasets from long to wide without having to list out a transpose statement

for each dataset?

I have multiple datasets that need to be transposed and as of now I have multiple transpose statements. For example:

proc transpose data = wide out = long prefix = gender_;
by location;
id sex ;
var percent ;
run;

proc transpose data = wide2 out = long2 prefix = age_;
by location;
id age;
var percent ;
run;

proc transpose data = wide3 out = long3 prefix = race;
by location;
id race;
var percent ;
run;

Anny assistance would be appreciated.

Re: Transform multiple datasets from long to wide

ballardw — Thu, 04 Aug 2022 21:35:34 GMT

Your data set names imply you want to make wide sets long. So which is it?

You may consider describing what you have done to get to this point and why as the multiple data sets and the code shown smack of a very inefficient process overall.

Plus, if you are making wide sets from long, in spite of your data set names, the question might well be why? You would be creating multiple data sets that are difficult to work with. So what is it you plan to do with those multiple "wide" sets that requires them to be wide?

Re: Transform multiple datasets from long to wide

luvscandy27 — Thu, 04 Aug 2022 21:45:45 GMT

I would like to make the long datasets wide. My apologies in the example I
entered them incorrectly.

Nothing happens before this point these data are received wide and need to
be transposed since that's how it's always been.

After they are transposed the datasets are sent to external stakeholders.

Re: Transform multiple datasets from long to wide

Reeza — Thu, 04 Aug 2022 22:03:27 GMT

Since the data set names are different and variables are different there's not much simplification. You could create a macro where you provide the parameters but it's not gaining you much IMO.

%macro flip(din = , dout = , var = );

proc transpose data= &din out= &dout prefix = &var._;
by location;
id &var.;
var percent;
run;

%mend;

%flip(din=wide, dout=long, var=gender);
%flip(din=wide2, dout=long2, var=age);
.....

Re: Transform multiple datasets from long to wide

ballardw — Thu, 04 Aug 2022 22:18:46 GMT

@luvscandy27 wrote:
I would like to make the long datasets wide. My apologies in the example I
entered them incorrectly.

Nothing happens before this point these data are received wide and need to
be transposed since that's how it's always been.

After they are transposed the datasets are sent to external stakeholders.

Am I the only one confused??? That appears to say "the data are received wide and we need to make it wide".

Perhaps a concrete example of a source file and the desired result are in order. Dummy values are fine as long as the source data resembles the structure of your current "received data set". Then show what the result should be.

Hint: "How it's always been" does not mean it is not time to change as there are new tools all the time. Such as Paper and Pencil instead of Chisels and Stone.

Re: Transform multiple datasets from long to wide

andreas_lds — Fri, 05 Aug 2022 04:49:02 GMT

@luvscandy27 wrote:
...

Nothing happens before this point these data are received wide and need to
be transposed since that's how it's always been.

...

@ballardw No,you are not the only one totally confused.

@luvscandy27 : Please post an example have dataset and the expected result.

Re: Transform multiple datasets from long to wide

Kurt_Bremser — Fri, 05 Aug 2022 05:21:16 GMT

Since you are sending the results to non-SAS users, use PROC REPORT

proc report data=long;
column location percent,sex;
define location / group;
define percent / "" analysis;
define sex / "" across;
run;

Use ODS to direct the output to the wanted file format. This combines the transpose and export into one step.