topic Re: re: stack columns in SAS Programming

re: stack columns

twildone — Mon, 30 Jan 2017 15:04:19 GMT

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

Re: re: stack columns

Astounding — Mon, 30 Jan 2017 15:10:11 GMT

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?

Re: re: stack columns

RW9 — Mon, 30 Jan 2017 15:31:03 GMT

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;

Re: re: stack columns

twildone — Mon, 30 Jan 2017 15:33:51 GMT

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;

Re: re: stack columns

Astounding — Mon, 30 Jan 2017 15:38:55 GMT

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;

Re: re: stack columns

twildone — Mon, 30 Jan 2017 15:50:18 GMT

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

Re: re: stack columns

Shmuel — Mon, 30 Jan 2017 15:55:23 GMT

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;

Re: re: stack columns

mkeintz — Mon, 30 Jan 2017 15:57:44 GMT

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;