topic Re: Three datasets, same variables, different values - need to combine into one in SAS Procedures

Three datasets, same variables, different values - need to combine into one

deleted_user — Wed, 09 Mar 2011 20:06:54 GMT

I have three different datasets: Males, Females and Siblings.
If there is a significant value present for variables A B C D E F then for the Males an "M" is present, Females an "F" is present and Siblings an "S" is present. I want to combine these three sets together so that if significant values are present for variables A B C D E F for Males, Females and Siblings, the values of lets say B for example would be "MFS". If D is only significant in Females and Siblings, then B would be "FS".
Not sure how to do this, have been doing it manually in excel and trying to save time.
Example of possible data:

Females
A B C D E F
1 F F
2

Males
A B C D E F
1 M
2

Siblings
A B C D E F
1 S S
2

Re: Three datasets, same variables, different values - need to combine into one

ballardw — Wed, 09 Mar 2011 20:41:48 GMT

What the output of the example data look like?

Re: Three datasets, same variables, different values - need to combine into one

sbb — Wed, 09 Mar 2011 20:50:57 GMT

After inputting your individual files, you can use SAS DATA step processing with the MERGE statement (possibly with a BY statement and key identification variables - maybe not?) And with the MERGE statement, you have an IN= keyword to tell SAS to reveal what file(s) contributed to the MERGE. Also, you must have a RENAME= on the MERGE (for each dataset to make SAS variables unique), or do so on the input side when you create the individual files.

Scott Barry
SBBWorks, Inc.

Re: Three datasets, same variables, different values - need to combine into one

SPR — Wed, 09 Mar 2011 21:26:28 GMT

Hello JHale,

I am not 100% sure I understood but anyway it is a solution:
[pre]
data Males;
input MA $ 1-1 MB $ 3-3 MC $ 5-5;
datalines;
1 M
run;
data Females;
input FA $ FB $ FC $;
datalines;
1 F F
run;
data Siblings;
input SA $ SB $ SC $;
datalines;
1 S S
run;
data c;
length A B C $4;
merge Males Females Siblings;
A=" ";
if MA = "M" then A=TRIM(A)||MA;
if FA = "F" then A=TRIM(A)||FA;
if SA = "S" then A=TRIM(A)||SA;
B=" ";
if MB = "M" then B=TRIM(B)||MB;
if FB = "F" then B=TRIM(B)||FB;
if SB = "S" then B=TRIM(B)||SB;
C=" ";
if MC = "M" then C=TRIM(C)||MC;
if FC = "F" then C=TRIM(C)||FC;
if SC = "S" then C=TRIM(C)||SC;
run;
[/pre]
Sincerely,
SPR

Re: Three datasets, same variables, different values - need to combine into one

Ksharp — Thu, 10 Mar 2011 05:24:47 GMT

Assuming variable A has unique value in all three datasets.

[pre]
data female;
infile datalines truncover;
input a b_f $ c_f $ d_f $ e_f $ f_f $ ;
datalines;
1 F F
2
;
run;
data male;
infile datalines truncover;
input a b_m $ c_m $ d_m $ e_m $ f_m $ ;
datalines;
1 M
2
;
run;
data sibling;
infile datalines truncover;
input a b_s $ c_s $ d_s $ e_s $ f_s $ ;
datalines;
1 S S
2
;
run;
data want;
merge female male sibling;
by a;
length b c d e f $ 10;
b=catt(of b_:);
c=catt(of c_:);
d=catt(of d_:);
e=catt(of e_:);
f=catt(of f_:);
keep a b c d e f;
run;

[/pre]

Ksharp