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 |
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;
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?
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;
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;
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;
Hi Astounding......it worked perfectly.....Thanks....
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.