Re-order columns after MERGE

Reply
New Contributor
Posts: 3

Re-order columns after MERGE


I have three data-sets:

Data Set 1:                                                                              Data Set 2:                                                                               Data Set 3:

NAME     JAN_COUNT     FEB_COUNT                                      NAME          JAN_A_PER          FEB_A_PER                            NAME          JAN_D_PER          FEB_D_PER

A               1                         3                                                   A                    10                              15                                   A                    90                         85

B               2                         4                                                   B                     .                                60                                   B                     .                          40

C               3                         5                                                   C                    30                               40                                   C                    70                         60

D               4                         6                                                   D                    70                               80                                   D                    30                         20

PROC SORT DATA =   Data_SET_1;

                                   BY NAME;

RUN;

PROC SORT DATA =   Data_SET_2;

                                   BY NAME;

RUN;

PROC SORT DATA =   Data_SET_3;

                                   BY NAME;

RUN;

DATA Merge;

          MERGE Data_SET_1 Data_SET_2 Data_SET_3;

          BY NAME;

Data Set Merge looks like this:

NAME     JAN_COUNT     FEB_COUNT      JAN_A_PER        FEB_A_PER       JAN_D_PER      FEB_D_PER

A               1                    3                         10                      15                    90                         85

B               2                    4                         .                         60                    .                            40

C               3                    5                         30                      40                    70                          60

D               4                    6                         70                       80                   30                           20

Is there a way to automatically have the columns in the data set order as follows?

NAME     JAN_COUNT     JAN_A_PER     JAN_D_PER     FEB_COUNT     FEB_A_PER     FEB_D_PER

A               1                    10                    90                    3                    15                         85

B               2                    .                         .                    4                     60                         40

C               3                    30                    70                    5                    40                         60

D               4                    70                    30                    6                    80                         20

Thank you for your responses.

Ani

Respected Advisor
Posts: 3,156

Re: Re-order columns after MERGE

Posted in reply to anirudhapande

Retain statement:

DATA Merge;

Retain NAME JAN_COUNT JAN_A_PER JAN_D_PER FEB_COUNT FEB_A_PER FEB_D_PER;

  MERGE Data_SET_1 Data_SET_2 Data_SET_3;

  BY NAME;

Run;

Haikuo

New Contributor
Posts: 3

Re: Re-order columns after MERGE

Thanks Hai.Kuo!

RETAIN works for static columns in all three tables. My data set basically tracks performance over a year so the number of columns will vary as i refresh the query every monh.

Any way to set this up to happen automatically as the data refreshes?

Thanks,

Ani


Respected Advisor
Posts: 3,156

Re: Re-order columns after MERGE

Posted in reply to anirudhapande

Something along the line of the following may help:

/*you need to create and maintain a format for the order purpose*/

proc format;

value $order

  'NAME'=0

'JAN_COUNT'=1

  'JAN_A_PER'=2

'JAN_D_PER'=3

'FEB_COUNT'=4

'FEB_A_PER'=5

'FEB_D_PER'=6

;

RUN;

/*Get the ordered variable names*/

PROC SQL;

  SELECT NAME INTO :_ORDER SEPARATED BY ' '

  FROM DICTIONARY.COLUMNS

  WHERE LIBNAME='YOURLIBREF'

AND MEMNAME CONTAINS 'DATA_SET_' /*or whatever naming rules*/

ORDER BY PUT(NAME,$ORDER.);

QUIT;

DATA Merge;

Retain &_order;

  MERGE Data_SET_1 Data_SET_2 Data_SET_3;

  BY NAME;

Run;

Haikuo

New Contributor
Posts: 3

Re: Re-order columns after MERGE

Posted in reply to anirudhapande

Give this a try.  It will dynamically create your columns based on the current month.

Data _null_;

mon = month(today());

Do I = 1 to mon;

  mdy = mdy(i,1,year(today()));

  call symput('mon'!!trim(left(i)),upcase(put(mdy,monname3.)));

End;

call symput('mon',trim(left(mon)));

Run;

%macro mycols;

retain name

%Do i = 1 %to &mon;

  &&mon&i.._COUNT

  &&mon&i.._A_PER

  &&mon&i.._D_PER

%end;

%mend;

data _null_;

%mycols;

run;

N/A
Posts: 1

Re: Re-order columns after MERGE

Posted in reply to anirudhapande

You can also use proc sql to place columns in a desired order, e.g.

proc sql;

  CREATE TABLE Merge AS

  SELECT a.NAME, a.JAN_COUNT,  b.JAN_A_PER, c.JAN_D_PER,  a.FEB_COUNT,  b.FEB_A_PER,  c.FEB_D_PER

  FROM (Data_SET_1 AS a FULL JOIN Data_SET_2 AS B ON a.NAME = b.NAME) FULL JOIN Data_SET_3 AS c ON a.NAME = c.NAME

  ORDER BY a.NAME;

quit;

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Re-order columns after MERGE

Posted in reply to anirudhapande

/*Write the varlist in the order you want*/

%let varlist=NAME  JAN_COUNT FEB_COUNT JAN_A_PER FEB_A_PER  JAN_D_PER FEB_D_PER;

data merge;
retain &varlist.;
set merge;
keep &varlist.;
run;

Ask a Question
Discussion stats
  • 6 replies
  • 390 views
  • 0 likes
  • 5 in conversation