topic Re: Combine/Merge two datasets in SAS Programming

Combine/Merge two datasets

rkumar23 — Mon, 12 Oct 2015 10:48:46 GMT

Would somebody give thoughts to achieve the following:

As below i have two datasets (dataset1 and dataset2) and trying to get the following output results based on Matched VSP_SER variable :(requirement is in the output dataset the blade_val1 etc should be replaced with the variable value from Blade_name1 etc ...

DATASET1

============
OBS   Vsp_ser year month day src date         time blade_val1 blade_val2 blade_val3 blade_val4
1        12345   2015 02    01   02   01-FEB-2015 0:15 11         11          11         11

DATASET2
Obs vsp_ser    Blade_Name1      Blade_Name2      Blade_Name3      Blade_Name4

37 12345 MPB1MA--MP001MA MPB1MA--MP011MA MPB1MA--MP021MA MPB1MA--MP031MA

output required :-

Vsp_ser year month day src date time MPB1MA--MP001MA MPB1MA--MP011MA MPB1MA--MP021MA MPB1MA--MP031MA
12345 2015 02 01 02 01-FEB-2015 0:15 11 11 11 11

Re: Combine/Merge two datasets

Jim_G — Mon, 12 Oct 2015 11:16:10 GMT

Try this code;

data a; informat date date12. time time8.;
input Vsp_ser year month day src date time
(blade_val1 blade_val2 blade_val3 blade_val4) (:$15.);
cards;
12345 2015 02 01 02 01-FEB-2015 0:15 11 11 11 11
;

DATA b;
input vsp_ser (Blade_Name1 Blade_Name2 Blade_Name3 Blade_Name4) (:$15.);
cards;
12345 MPB1MA--MP001MA MPB1MA--MP011MA MPB1MA--MP021MA MPB1MA--MP031MA
;
proc print; run;

proc sort data=a out=a; by vsp_ser;

proc sort data=b out=b; by vsp_ser;

data c; merge a(in=ina) b(in=inb); by vsp_ser ;
if ina and inb then do;
blade_val1=blade_name1;
blade_val2=blade_name2;
blade_val3=blade_name3;
blade_val4=blade_name4;
output;
end; drop bladename1-bladename4;
proc print; run;

Jim

Re: Combine/Merge two datasets

rkumar23 — Mon, 12 Oct 2015 12:06:35 GMT

sorry Jim,it doesn't work.

Re: Combine/Merge two datasets

Astounding — Mon, 12 Oct 2015 13:32:41 GMT

What you are asking for will be clumsy at best, impossible at worst. Are you really asking for a variable name of "MPB1MA--MP001MA"??? That is possible, but will be quite clumsy to work with later.

What happens when you have a second Vsp_ser in your data? Would the variable name "MPB1MA--MP001MA" even apply to that observation? Would it always match the value from BLADE1? At a minimum, you will need to spell out what the solution should look like when you have more than one observation in your data set.

Re: Combine/Merge two datasets

Jim_G — Mon, 12 Oct 2015 20:31:44 GMT

You want to take the data values from dataset2 and make them variable names (column headings) in dataset1. I think that could be done in SAS by pushing the data values into macro variables and having macros write SAS code. As Astounding said this is not done in normal data processing, You could have a different set of variable names for each obs. I know that you have an objective but I think you need a different approach Jim

Re: Combine/Merge two datasets

Patrick — Mon, 12 Oct 2015 22:11:56 GMT

If you just want to print variable names with a more user friendly way then define labels (this could also be done dynamically using SAS code and your DATESET2).

...but: Looking at your data do I understand right that the names of the variables like "Blade_Name1" could have different "labels" based on the value of vsp_ser?

Do you really need the "new names" in a new data set or is this more about reporting and you just need a report with such column headidngs?

Re: Combine/Merge two datasets

rkumar23 — Tue, 13 Oct 2015 04:52:42 GMT

I need to replace the variables name with variable values so have to look at some way of doing it via MACRO ...

Re: Combine/Merge two datasets

Patrick — Tue, 13 Oct 2015 07:19:57 GMT

@rkumar23

To provide you with a solution we need to fully understand the problem. The questions I've asked you are relevant for how to solve this. Can you please answer them?

Re: Combine/Merge two datasets

Astounding — Tue, 13 Oct 2015 18:52:12 GMT

In that case, I still need to see what your solution would look like when you have two observations instead of one, in the incoming data sets.

Re: Combine/Merge two datasets

Jim_G — Sun, 18 Oct 2015 14:37:13 GMT

Look at this thread and see if the solution could work in your case.

https://communities.sas.com/t5/Base-SAS-Programming/Weird-data-manipulation-Need-help/m-p/230412#U230412

Re: Combine/Merge two datasets

Jim_G — Sun, 18 Oct 2015 20:15:55 GMT

data a; informat date date12. time time8.;
input Vsp_ser year month day src date time
(blade_val1 blade_val2 blade_val3 blade_val4) (:$15.);
cards;
12345 2015 02 01 02 01-FEB-2015 0:15 11 11 11 11
;

DATA b;
input vsp_ser (Blade_Name1 Blade_Name2 Blade_Name3 Blade_Name4) (:$15.);
cards;
12345 MPB1MA--MP001MA MPB1MA--MP011MA MPB1MA--MP021MA MPB1MA--MP031MA
;
proc print; run;

proc sort data=a out=a; by vsp_ser;

proc sort data=b out=b; by vsp_ser;

data c; merge a(in=ina) b(in=inb); by vsp_ser ;
if ina and inb then do;
key=blade_name1; value=blade_val1; output;
key=blade_name2; value=blade_val2; output;
key=blade_name3; value=blade_val3; output;
key=blade_name4; value=blade_val4; output;
end; keep vsp_ser key value;
proc print; id vsp_ser key value; run;
proc transpose data=c out=wide(drop=_:);
by vsp_ser notsorted;
id key;
var value;
run;
Proc print; run;