topic Merge 2 datasets and get 3 outputs in SAS Studio

Merge 2 datasets and get 3 outputs

jude1 — Mon, 08 Mar 2021 19:47:02 GMT

I am trying to merge the 2 datasets by var1 and create 3 datasets outputs, one that has var1 in both, one that has var1 with no income, one that has var1 with no grad_year

the data looks like this:

data mydata;

input var1 income;

datalines;

1 400

2 500

3 500

7 600

8 900

9 1000

run;

data mydata2;

var1 grad_year

1 2018

2 2019

4 2020

5 2019

6 2017

11 2019

merging is not an issue for me. I think i should use the 'if then output' statement for this but i could not figure it out.

Thanks

Re: Merge 2 datasets and get 3 outputs

Shmuel — Tue, 09 Mar 2021 05:55:08 GMT

Use next code:

data both no_income no_grad_year;
 merge mydata(in=in1)
           mydata2(in=in2)
     ;
   by var1;
         if in1 and in2 then output both; else    /* line edited */
         if in1 and not in2 then output no_grad_year; 
         else output no_income ;
run;

Re: Merge 2 datasets and get 3 outputs

mkeintz — Mon, 08 Mar 2021 20:03:39 GMT

Consider using the IN= parameters on the MERGE statement, as in:

data both  noincome nogradyear;
  merge mydata (in=in1) mydata2 (in=in2);
  by var1;
   ....
run;

Do tests on the values of IN1 and IN2, and then conditional OUTPUT statements based on those tests.

Re: Merge 2 datasets and get 3 outputs

jude1 — Mon, 08 Mar 2021 21:37:03 GMT

Thanks for your help. So I wrote the code and countered a new problem. i keep getting error 22-322 and 202-322 on the data2 if statement. data1 and data3 are coming out correct, but not data2. any tips?

data data1 data2 data3;
merge mydata1(in=a) mydata2(in=b); by var1;
if a and b; output data1;
if b and not a then output data2; else output data3;
run;
proc print data=data1;
proc print data=data2;
proc print data= data3;

Re: Merge 2 datasets and get 3 outputs

JackHamilton — Mon, 08 Mar 2021 21:55:17 GMT

In your code (reformatted to make it easier to read):

data data1 data2 data3;
    merge mydata1(in=a) 
          mydata2(in=b); by var1;
if a and b; 
output data1;
if b and not a then 
    output data2; 
else 
    output data3;
run;

When do you think "if b and not a" will be true? The line "if a and b" has already told SAS that you want only those records where both a and b are true. So you won't get any records where b is false. No code after a false subsetting if will be executed (with the exception of subroutines).

Re: Merge 2 datasets and get 3 outputs

PGStats — Mon, 08 Mar 2021 21:57:53 GMT

Replace

if a and b; output data1;

with

if a and b then output data1;

Re: Merge 2 datasets and get 3 outputs

Shmuel — Tue, 09 Mar 2021 05:54:13 GMT

Sorry, I had a typo and missed the word THEN.

I have edited and corrected my first post.

Re: Merge 2 datasets and get 3 outputs

Sonia2 — Tue, 29 Mar 2022 02:29:27 GMT

I got the same question in exam but I want to clear some concepts, In same question can I use if in1 and in2 then output both; if in1 and not in2 then output no_grad_year;
If not in1 and in2 then output no_income ;
And my second question is when we give answer to question in exam if we look at else if in1 and not in2 then output no_grad_year; in output we will get some missing values if I want answer to question and answer is missing value then I’ll write for or type missing value or what ?? Please explain me.

Re: Merge 2 datasets and get 3 outputs

Shmuel — Tue, 29 Mar 2022 05:46:57 GMT

Your post:

@Sonia2 wrote:
I got the same question in exam but I want to clear some concepts, In same question can I use if in1 and in2 then output both; if in1 and not in2 then output no_grad_year;
If not in1 and in2 then output no_income ;
And my second question is when we give answer to question in exam if we look at else if in1 and not in2 then output no_grad_year; in output we will get some missing values if I want answer to question and answer is missing value then I’ll write for or type missing value or what ?? Please explain me.

Please notice that IN1 and IN2 - each contains 1 for EXIST and 0 for absent.

As long as you didn't arrive to RETURN statement or to the end of the program, the data is still in the memory and you can write it to output, either the same dataset (making duplicates) or to other dataset.

Missing value in a variable is the result of nonexistance record on the dataset that contains this variable.