DATA Step, Macro, Functions and more

re: stack columns

Accepted Solution Solved
Reply
Regular Contributor
Posts: 222
Accepted Solution

re: stack columns

Hi....I am trying to convert a dataset by stacking the columns which represent different fiscal years into one column. I was thinking of using a macro as the last fiscal year will change and not always known. The fiscal year variables are character. Thanks.

 

Have:

Classification 201213 201314 201415 201516
0 - 1000  6,881  6,436  7,495  6,334
1001 - 2000  740  772  810  740
2001 - 3000  850  812  819  744
3001 - 4000  990  972  902  892
4001 - 5000  980  995  951  940

 

 

Want:

Classification FiscalYear Clients
0 - 1000 201213  6,881
0 - 1000 201314  6,436
0 - 1000 201415  7,495
0 - 1000 201516  6,334
1001 - 2000 201213  740
1001 - 2000 201314  772
1001 - 2000 201415  810
1001 - 2000 201516  740
2001 - 3000 201213  850
2001 - 3000 201314  812
2001 - 3000 201415  819
2001 - 3000 201516  744
3001 - 4000 201213  990
3001 - 4000 201314  972
3001 - 4000 201415  902
3001 - 4000 201516  892

Accepted Solutions
Solution
‎01-30-2017 10:50 AM
Super User
Posts: 5,084

Re: re: stack columns

I was thinking of something like RW9's solution, but without knowledge of the variable names.  For example:

 

data want;

if 5=4 then set have (drop=classification);

array years {*} _character_;

set have;

do _n_=1 to dim(years);

   FiscalYear = vname(years{_n_});

   Clients = years{_n_};

   output;

end;

keep Classification FiscalYear Clients;

run;

View solution in original post


All Replies
Super User
Posts: 5,084

Re: re: stack columns

This can absolutely be done without macros, depending on these questions.

 

First, what are the actual variable names in your data set?  Unless you have taken special measures, SAS would not normally use variable names that begin with a number.

 

Second, are there any other character variables in your incoming data, in addition to the ones you have shown here?

Regular Contributor
Posts: 222

Re: re: stack columns

Hi Astounding,

 

No there are no other character variables in the dataset and only include the classification and the different fiscal year data. Below is thew code that retrieves the data:

 

DATA WORK.Clients_Data;

   LENGTH

       'Classification'n $ 15

       '201213'n        $ 8

       '201314'n        $ 8

       '201415'n        $ 8

       '201516'n        $ 8;

   FORMAT

       'Classification'n $CHAR15.

       '201213'n       $CHAR8.

       '201314'n       $CHAR8.

       '201415'n       $CHAR8.

       '201516'n       $CHAR8.;

   INFORMAT

       'Classification'n $CHAR15.

       '201213'n       $CHAR8.

       '201314'n       $CHAR8.

       '201415'n        $CHAR8.

       '201516'n       $CHAR8.;

   INFILE 'C:\Users\AppData\Local\Temp\SEG7624\Clients Data-6dcfb50cc4494588883d694debb90b76.txt'

       LRECL=69

       ENCODING="WLATIN1"

       TERMSTR=CRLF

       DLM='7F'x

       MISSOVER

       DSD ;

   INPUT

       'Classification'n : $CHAR15.

       '201213'n        : $CHAR8.

       '201314'n        : $CHAR8.

       '201415'n        : $CHAR8.

       '201516'n        : $CHAR8.;

RUN;

Super User
Super User
Posts: 7,404

Re: re: stack columns

Try to avoid thinking, this is hard, must use macro.  Macro does nothingb but generate code.  Also as pointed out before post test data in the form of a datastep.  We are now guessing your data structure.

 

Now in your given test data, I am assuming there is an underscore:

data want;
  set have;
  array vals{4} _201:;
  do i=1 to 4;
    fiscalyear=vname(vals{i});
    clients=vals{i};
    output;
  end;
run;
Solution
‎01-30-2017 10:50 AM
Super User
Posts: 5,084

Re: re: stack columns

I was thinking of something like RW9's solution, but without knowledge of the variable names.  For example:

 

data want;

if 5=4 then set have (drop=classification);

array years {*} _character_;

set have;

do _n_=1 to dim(years);

   FiscalYear = vname(years{_n_});

   Clients = years{_n_};

   output;

end;

keep Classification FiscalYear Clients;

run;

Regular Contributor
Posts: 222

Re: re: stack columns

Hi Astounding......it worked perfectly.....Thanks....

Trusted Advisor
Posts: 1,383

Re: re: stack columns

Some more questions:

-  does piscal year 201213 means 2012-2013 ? 

- how many colums of piscal-years do you have ? (there are 4 columns in test data)

- assuming your origin is excel then colomn names are probably col1, col2, col3 ...etc/

 

assuming positive answer then:


data have;
 input classification $12.
       col1-col4;
datalines;
0-1000      6881 6436 7495 6334
1001 - 2000 740 772 810 740
2001 - 3000 850 812 819 744
; run;
  
%let max_col = 4;
%let year_start = 2012;

%macro labels;
       %let year = &year_start;
       %do i=1 %to &max_col;
               %let next_year = %eval(&year +1);
               %global lbl&i;
               %let lbl&i = &year.%substr(&next_year,3,2);
               %let year = &next_year; 
        %end;
%mend labels;
%labels;

data want;
 set have;
     array colx col1-col&max_col;
     
     do i=1 to &max_col;
        FiscalYear = symget(compress('lbl'||left(i)));
        Clients = colx(i);
        output;
    end;
    keep Classification FiscalYear Clients;
run;

                

 

Valued Guide
Posts: 797

Re: re: stack columns

Probably the most appropriate technique is proc transpose:

 

data have;
  input Classification :$&11.  FY201213 :comma5.0  FY201314 :comma5.0 FY201415 :comma5.0 FY201516 :comma5.0;
datalines; 
0 - 1000  6,881  6,436  7,495  6,334 
1001 - 2000  740  772  810  740 
2001 - 3000  850  812  819  744 
3001 - 4000  990  972  902  892 
4001 - 5000  980  995  951  940 
run;
 
proc transpose data=have 
              out=want (rename=(col1=clients _name_=fiscal_year)) ;
  by classification notsorted;
run;

 

 

xx

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 258 views
  • 2 likes
  • 5 in conversation