topic How to merge data sets in SAS Programming

How to merge data sets

dustychair — Sun, 19 Jul 2020 06:44:19 GMT

Hi all,

I have about 48 data sets named

pfreq_and_pl_g1_gr1

pfreq_and_pl_g1_gr2

pfreq_and_pl_g1_gr3

pfreq_and_pl_g1_gr4

pfreq_and_pl_g2_gr1

pfreq_and_pl_g2_gr2

...

"g" goes up to 12.

and "gr" goes up to 4.

I want to merge them based on the "g". For example one merged data set will include (pfreq_and_pl_g1_gr1, pfreq_and_pl_g1_gr2, pfreq_and_pl_g1_gr3, pfreq_and_pl_g1_gr4), the other data set will include (pfreq_and_pl_g2_gr1, pfreq_and_pl_g2_gr2, pfreq_and_pl_g2_gr3, pfreq_and_pl_g2_gr4).

Merging will be based on the data set's name, not a variable. How can I do that?

Thank you

Re: How to merge data sets

Kurt_Bremser — Sun, 19 Jul 2020 06:47:04 GMT

If those datasets share the same structure, you probably want to.stack them, not merge.

Re: How to merge data sets

dustychair — Sun, 19 Jul 2020 07:39:40 GMT

Thank you @Kurt_Bremser. I am sure there is an easier way to do that. Here is what I did. It worked but I still had to play a little bit in excel.

%macro combine(d);

%do i = 1 %to 12;
data npfreq_and_pl_g&i._gr1;
set npfreq_and_pl_g&i._gr1;
group=1;

data npfreq_and_pl_g&i._gr2;
set npfreq_and_pl_g&i._gr2;
group=2;

data npfreq_and_pl_g&i._gr3;
set npfreq_and_pl_g&i._gr3;
group=3;

data npfreq_and_pl_g&i._gr4;
set npfreq_and_pl_g&i._gr4;
group=4;
run;
data final_grade_&i.;
set npfreq_and_pl_g&i._gr1 npfreq_and_pl_g&i._gr2 npfreq_and_pl_g&i._gr3 npfreq_and_pl_g&i._gr4;
%end;
run;
%mend;
%combine ;

Re: How to merge data sets

Kurt_Bremser — Sun, 19 Jul 2020 08:34:29 GMT

Try this:

%macro dsnames;
%do i = 1 %to 12;
  %do j = 1 %to 4;
  npfreq_and_pl_g&i._gr&j.
  %end;
%end;
%mend;

data final_grade;
set
  %dsnames
  indsname=dsname
;
group = input(scan(dsname,-1,'r'));
run;

not tested, posted from my tablet.