BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
twildone
Pyrite | Level 9

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
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

7 REPLIES 7
Astounding
PROC Star

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?

twildone
Pyrite | Level 9

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Astounding
PROC Star

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;

twildone
Pyrite | Level 9

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

Shmuel
Garnet | Level 18

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;

                

 

mkeintz
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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