BookmarkSubscribeRSS Feed
rkumar23
Calcite | Level 5

 

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

10 REPLIES 10
Jim_G
Pyrite | Level 9

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

rkumar23
Calcite | Level 5
sorry Jim,it doesn't work.
Astounding
PROC Star

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.

Jim_G
Pyrite | Level 9

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

Patrick
Opal | Level 21

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?

rkumar23
Calcite | Level 5

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

Patrick
Opal | Level 21

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

Astounding
PROC Star

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.

Jim_G
Pyrite | Level 9

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

 

Jim_G
Pyrite | Level 9

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1432 views
  • 0 likes
  • 4 in conversation