Search code examples
sastransposedata-cleaningmerging-data

Extract 2 Columns and Attach as Rows in SAS

I have two datasets in SAS. The first looks like this (let's say it is called data 1 (I'm only concerned with two columns of it)

enter image description here

...and the second dataset (let's say it is called data 2) looks like this:

enter image description here

...and I am trying to extract the second column of the first dataset and insert it into the second dataset, to achieve something that looks like this:

Description

Basic Problem Description:

I am trying to extract two columns from a dataset in SAS and add them as rows to a second dataset. The variable names in the first dataset are in a column of their own (entitled 'variable name') and in the second dataset each variable is a column header (a variable in itself) with corresponding data. The images I provided are overly simplistic, as the actual data itself is very long. Basically, I am trying to find functions in SAS which allow me to do this.

What I have tried

-I have tried to extract the first two columns as a table using proc sql, converted them to a data frame using a data step, sorted them, then used proc transpose to try to convert them from long to wide, then tried to use some sort of append function to tack them on to the second dataset, but append did not work.

-I have tried to merge the two sets, but the merge does not seem to work after using proc transpose.

-I have also tried transposing the second dataset and then merging them, which worked (for some reason) but then I was not able to transpose the data back (so that I can analyze it, which is my purpose in doing all of this).

What functions would I use to go about this process?

Apologies for not providing replicable data, I am more searching for recommendations for functions rather than a detailed hard solution.

Solution

  • To force PROC TRANSPOSE to use a variable as the source for the new variable names use the ID statement. So if you have this first dataset:

    data tall;
   input fruit $ count @@;
cards;
APPLE 1 PEACH 2 PEAR 2
;

    You can use this code to convert it.

    proc transpose data=tall out=wide;
   id fruit;
   var count;
run;

    Then if you have another dataset that already has the variables APPLE, PEACH, PEAR etc then just set the two together.

    data want;
  set wide have ;
run;