topic Re: Melting a dataset in SAS Programming

Melting a dataset

carles — Thu, 09 May 2019 12:00:17 GMT

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,

Re: Melting a dataset

PaigeMiller — Thu, 09 May 2019 12:03:16 GMT

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;

Re: Melting a dataset

Astounding — Thu, 09 May 2019 12:04:15 GMT

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.

Re: Melting a dataset

data_null__ — Thu, 09 May 2019 13:32:05 GMT

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;

Re: Melting a dataset

carles — Fri, 10 May 2019 09:11:52 GMT

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