Help using Base SAS procedures

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

Reply
N/A
Posts: 0

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

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
Super User
Posts: 11,343

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

Posted in reply to deleted_user
What the output of the example data look like?
Super Contributor
Super Contributor
Posts: 3,174

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

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.
Super Contributor
Super Contributor
Posts: 365

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

Posted in reply to deleted_user
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
Super User
Posts: 10,023

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

Posted in reply to deleted_user
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_Smiley Happy;
c=catt(of c_Smiley Happy;
d=catt(of d_Smiley Happy;
e=catt(of e_Smiley Happy;
f=catt(of f_Smiley Happy;
keep a b c d e f;
run;

[/pre]


Ksharp
Ask a Question
Discussion stats
  • 4 replies
  • 123 views
  • 0 likes
  • 5 in conversation