how to match values from one dataset to another using named variables

Reply
New Contributor
Posts: 2

how to match values from one dataset to another using named variables

Hi

I'm trying to merge two datasets together, one contains a unique key (person number) and 3 referencial fields labelled 1 to 3, this has been transposed from a previous proc contents. I need to match / merge the data from this one to another one (also has person number), and also has the true variable values for the referenced fields in the other datasets. I'm not sure how to do this at all, I've looked at vvaluex, but the data can be of varying formats and it threw up an error.

Person numberreference onereference 2
1date of birthemployment
2school attendedsex
3sexdate of birth
Person numberSexDate of BirthSchool AttendedEmployment
1F01/01/2001st johnsnone
2M01/01/1990box streetarmy
3F01/01/1970young schoolretired

I need to return the below

Person NumberReference OneReference 2
101/01/2001none
2box streetM
3F01/01/1970

Using sas 9.1

Thanks for help in advance

Respected Advisor
Posts: 3,799

Re: how to match values from one dataset to another using named variables

VVALUEX

data name;
   infile cards dsd;
  
input id (name1 name2)(:$32.);
   cards;
1,dob,employment
2,school,sex
3,sex,dob
;;;;
   run;
data demo;
   infile cards dsd;
  
input id sex:$1. dob:mmddyy. school:$32. employment:$32.;
  
format dob date9.;
  
cards;  
1,F,01/01/2001,st johns,none
2,M,01/01/1990,box street,army
3,F,01/01/1970,young school,retired
;;;;
   run;
data ref(keep=id ref1 ref2);
   merge name demo;
   by id;
   length ref1-ref2 $64;
   ref1 = vvaluex(name1);
   ref2 = vvaluex(name2);
  
run;
New Contributor
Posts: 2

Re: how to match values from one dataset to another using named variables

Posted in reply to data_null__

Thank you, this helps a lot, however, I'm ok with this example as it only has a few variables and can be coded easily. I have some datasets that contain 100 variables that I need to do the same with, would you suggest I create a list somehow and then call the list into the above??

Thanks again

Super User
Super User
Posts: 7,039

Re: how to match values from one dataset to another using named variables

The number of variables in the file DEMO in the example from data_null_ is unlimited and no change is needed to support whatever data you already have.

To make the number of "reference" variables larger use array processing.  It will work better if you set fixed number, but you can also calculalate the number in a previous step (without having to read the actual data, just the metadata).

%let numref=100;

data ref(keep=id refSmiley Happy;
   merge name demo;
   by id;
   array names name: ;

   array ref (&numref) $64;

   do i=1 to dim(names);

     ref(i) = vvaluex(names(i));

    end;

run;

Ask a Question
Discussion stats
  • 3 replies
  • 255 views
  • 1 like
  • 3 in conversation