topic Re: how to match values from one dataset to another using named variables in SAS Programming

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

loullj — Mon, 15 Apr 2013 19:16:12 GMT

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 number	reference one	reference 2
1	date of birth	employment
2	school attended	sex
3	sex	date of birth

Person number	Sex	Date of Birth	School Attended	Employment
1	F	01/01/2001	st johns	none
2	M	01/01/1990	box street	army
3	F	01/01/1970	young school	retired

I need to return the below

Person Number	Reference One	Reference 2
1	01/01/2001	none
2	box street	M
3	F	01/01/1970

Using sas 9.1

Thanks for help in advance

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

data_null__ — Mon, 15 Apr 2013 21:40:30 GMT

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;

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

loullj — Mon, 15 Apr 2013 21:56:15 GMT

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

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

Tom — Mon, 15 Apr 2013 22:42:15 GMT

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 ref:);
   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;