As a matter of an example I have the following dataset and I want what is shown in the right side of the arrow:
My idea was to
(1) make a macro to select the colnames of whatever dataset table and put it into an array;
(2) make a macro that you input a column and returns two columns (one with the colname and the other with the values)
(3) make a macro that concatenates / merges two datasets into another.
Then iterably getting the result.
I am having troubles even to get the names of a table so I really kindly ask for help.
Condition: Can't used proc iml or R language.
Here I put the dataCode
DATA WORK.dat1; INPUT A B C; datalines; 0 0 0 0 2 0 0 0 0 0 1 0 1 0 2 1 2 1 1 1 1 1 1 1 ;
Can someone help me in the writing of the code for the macros ? No need for the iteration part since I can do it
Thank you in advance for so much help,
I'm not sure I would want to complicate this by adding macro language. How about:
data want;
set have;
array nums {*} _numeric_;
do _n_=1 to dim(nums);
colnames = vname(nums{_n_});
values = nums{_n_};
output;
end;
keep colnames values;
run;
proc sort data=want;
by colnames;
run;
This processes numeric variables only, but you would need to process character variables separately. You could not use a single variable (VALUES) to hold both numeric and character values.
Let me point out that there is no need for macros here, re-arranging your data set can almost always be performed with data step operations, and/or PROC TRANSPOSE.
This gives the result you want, you will need to sort data set WANT to your final required condition.
data want;
set dat1;
colnames='A';
value=a;
output;
colnames='B';
value=b;
output;
colnames='C';
value=c;
output;
keep colnames value;
run;
I'm not sure I would want to complicate this by adding macro language. How about:
data want;
set have;
array nums {*} _numeric_;
do _n_=1 to dim(nums);
colnames = vname(nums{_n_});
values = nums{_n_};
output;
end;
keep colnames values;
run;
proc sort data=want;
by colnames;
run;
This processes numeric variables only, but you would need to process character variables separately. You could not use a single variable (VALUES) to hold both numeric and character values.
DATA WORK.dat1;
INPUT A B C;
datalines;
0 0 0
0 2 0
0 0 0
0 1 0
1 0 2
1 2 1
1 1 1
1 1 1
;
proc print;
run;
proc transpose out=test name=colnames;
run;
proc transpose out=test(rename=col1=values drop=_:);
by colnames notsorted;
run;
proc print;
run;
Thank you so much for the answer, this indeed works as well as the other I have accepted as answer.
Nevertheless, I could only give one accepted answer, but thank you for the time spent in the question
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.