DATA Step, Macro, Functions and more

Combine/Merge two datasets

Reply
Frequent Contributor
Posts: 78

Combine/Merge two datasets

[ Edited ]

 

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 Smiley Sadrequirement 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

Frequent Contributor
Posts: 95

Re: Combine/Merge two datasets

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

Frequent Contributor
Posts: 78

Re: Combine/Merge two datasets

sorry Jim,it doesn't work.
Super User
Posts: 5,498

Re: Combine/Merge two datasets

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.

Frequent Contributor
Posts: 95

Re: Combine/Merge two datasets

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

Respected Advisor
Posts: 4,173

Re: Combine/Merge two datasets

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?

Frequent Contributor
Posts: 78

Re: Combine/Merge two datasets

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

Respected Advisor
Posts: 4,173

Re: Combine/Merge two datasets

@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?

Super User
Posts: 5,498

Re: Combine/Merge two datasets

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.

Frequent Contributor
Posts: 95

Re: Combine/Merge two datasets

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#U23...

 

Frequent Contributor
Posts: 95

Re: Combine/Merge two datasets

[ Edited ]

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=_Smiley Happy;
by vsp_ser notsorted;
id key;
var value;
run;
Proc print; run;

Ask a Question
Discussion stats
  • 10 replies
  • 374 views
  • 0 likes
  • 4 in conversation