topic Re: Match numeric values in results with reference file containing string values in SAS Programming

Match numeric values in results with reference file containing string values

Epi_Stats — Fri, 26 Jan 2024 13:21:43 GMT

Hi,

I have a results output with study participant IDs and numeric values

data results;
input ID resultA resultB;
datalines;
1 2392 887
2 1587 902
3 2392 234
4 4035 2392
;
run;

These numeric values correspond to string values (names) found in a reference file

data reference;
input num first$ last$;
datalines;
2392 joe bloggs
4035 mary smith
1587 bloggs joe
234 sherlock holmes 
902 john watson
887 benjamin button
;
run;

I want to match the string values in the reference file with the numeric values in the results output.
This is the output I want to achieve; the string values under resultA and resultB are based
on the numbers and corresponding string values in the reference file

data want;
input ID resultA :$200. resultB :$200. ;
datalines;
1 joe-bloggs benjamin-button
2 bloggs-joe john-watson
3 joe-bloggs sherlock-holmes
4 mary-smith joe-bloggs
;
run;

I have tried to search for code on the forum to solve this, with no success.

Any help would be greatly appreciated.

Please let me know if my problem is not clear from the above.

Re: Match numeric values in results with reference file containing string values

Aku — Fri, 26 Jan 2024 13:51:05 GMT

You have to join Results and Reference tables. I'm suggesting to use proc SQL for joining because there is two joins and tables doesn't have common variable.

data results;

input ID resultA resultB;

datalines;

1 2392 887

2 1587 902

3 2392 234

4 4035 2392

;

run;

data reference;

input num first $ last $;

datalines;

2392 joe bloggs

4035 mary smith

1587 bloggs joe

234 sherlock holmes

902 john watson

887 benjamin button

;

run;

proc sql;

create table want as

select R.ID,

Catx('-',r1.First,r1.Last) as First,

Catx('-',r2.First,r2.Last) as Second

from Results R

left join Reference R1

on r.ResultA = r1.num

left join Reference R2

on r.ResultB = r2.num

order by r.ID;

quit;

Re: Match numeric values in results with reference file containing string values

Sajid01 — Fri, 26 Jan 2024 14:05:14 GMT

Hello
Based upon your first two datasets results and reference the outcome does not appear to be what you have shown.
However using the sample code you should be able to get the desired result as shown

data results;
input ID resultA resultB;
datalines;
1 2392 887
2 1587 902
3 2392 234
4 4035 2392
;
run;
data reference;
input num first$ last$;
datalines;
2392 joe bloggs
4035 mary smith
1587 bloggs joe
234 sherlock holmes 
902 john watson
887 benjamin button
;
run;

proc sql;
Select id, cat(Propcase(strip(first)),"-",propcase(Strip(last))) as Name from results a,reference b
where a.resultA=b.num;
quit;

The code gives result like this with the given datasets results and reference.

Re: Match numeric values in results with reference file containing string values

FreelanceReinh — Fri, 26 Jan 2024 14:08:52 GMT

Hi @Epi_Stats,

I would probably create a custom format:

data fmt(drop=first last);
retain fmtname 'ref';
set reference(rename=(num=start));
length label $40;
label=catx('-',first,last);
run;

proc format cntlin=fmt;
run;

Then you may not even need dataset WANT because you can get the same information from the existing RESULTS dataset:

proc print data=results;
format res: ref.;
run;

Creating dataset WANT is not much work either:

data want(drop=_:);
set results(rename=(resultA=_A resultB=_B));
length resultA resultB $200;
resultA=put(_A, ref.);
resultB=put(_B, ref.);
run;

Re: Match numeric values in results with reference file containing string values

Epi_Stats — Fri, 26 Jan 2024 14:47:57 GMT

Thank you @FreelanceReinh ,

Very clever to treat the reference file as a format - I had not thought of this!