topic Re: combining 2 rows of same VAR1 value but dif VAR2 and VAR3 values in SAS Enterprise Guide

combining 2 rows of same VAR1 value but dif VAR2 and VAR3 values

Primohunter — Thu, 06 Oct 2022 09:43:33 GMT

Hello everyone,

below you can find an example of a data set that i want to turn to the OUTPUT example below as well.

Have DATASET:

Var1 Var2 Var3 Var4

Nick ran1 Y N

Nick ran1 N Y

John ran2 Y N

John ran2 N Y

George ran3 N Y

Output (Want) Dataset:

Var1 Var2 Var3 Var4

Nick ran1 Y Y

John ran2 Y Y

George ran3 N Y

Re: combining 2 rows of same VAR1 value but dif VAR2 and VAR3 values

PaigeMiller — Thu, 06 Oct 2022 09:52:43 GMT

Please explain the logic that allows you to transform the HAVE data set into the WANT data set.

Re: combining 2 rows of same VAR1 value but dif VAR2 and VAR3 values

Primohunter — Thu, 06 Oct 2022 10:02:42 GMT

lets just say that for some reason nick and john are both qualified for having VAR 3 AND VAR 4 (e.g. 'Y') but the input program could only accept only one VAR input at a time.

For example

nick wears a black and white shirt

so the dataset becomes as follows:

PROBLEMATIC INPUT DESIRED FORMAT FOR FURTHER ANALYSIS

Name Status White Black ---> Name Status White Black

Nick Dressed Y N Nick Dressed Y Y

Nick Dressed N Y

Re: combining 2 rows of same VAR1 value but dif VAR2 and VAR3 values

PaigeMiller — Thu, 06 Oct 2022 10:11:20 GMT

This would be much easier if you used 0s and 1s instead of N and Y.

data have;
    input var1 $ var2 $ var3 var4;
    cards;
nick ran1 1 0
nick ran1 0 1
john ran2 1 0
john ran2 0 1
george ran3 0 1
;
proc summary data=have nway;
    class var1 var2;
    var var3 var4;
    output out=want max=;
run;

As a side issue, please don't use different variable names in your examples than in your original data set.

Re: combining 2 rows of same VAR1 value but dif VAR2 and VAR3 values

Primohunter — Thu, 06 Oct 2022 10:14:37 GMT

Thanks for your response
will keep that in mind. i just used different real life variable names to make the reasoning easier.

Is there another way to do it with those character variables?

Re: combining 2 rows of same VAR1 value but dif VAR2 and VAR3 values

PaigeMiller — Thu, 06 Oct 2022 10:18:16 GMT

@Primohunter wrote:
Thanks for your response
will keep that in mind. i just used different real life variable names to make the reasoning easier.

Reasoning is easier if you use the same variable names in your examples and actual data. We're trying to help you, but you have to help us too.

Is there another way to do it with those character variables?

Convert char to numeric, use PROC SUMMARY as I have shown, and then convert 0 and 1 back to N and Y.

Re: combining 2 rows of same VAR1 value but dif VAR2 and VAR3 values

FreelanceReinh — Thu, 06 Oct 2022 10:40:23 GMT

@Primohunter wrote:
Is there another way to do it with those character variables?

You could use PROC SQL, but the syntax is less elegant than the succinct "max=" of PROC SUMMARY's OUTPUT statement:

data have;
input (var1-var4)($);
cards;
Nick ran1 Y N
Nick ran1 N Y
John ran2 Y N
John ran2 N Y
George ran3 N Y
;

proc sql;
create table want as
select var1, var2, max(var3) as var3, max(var4) as var4
from have
group by 2, 1;
quit;

The "maximum" here is based on the alphabetical order 'N' < 'Y'.

Re: combining 2 rows of same VAR1 value but dif VAR2 and VAR3 values

PaigeMiller — Thu, 06 Oct 2022 11:40:49 GMT

And so I still believe, despite the fine example from @FreelanceReinh , that handling binary variables via numeric 0s and 1s is superior for a number of reasons, and I urge all readers of this thread to use numeric 0s and 1s for binary variables.

If the real problem has 25 variables, instead of the 4, much less typing in PROC SUMMARY than in PROC SQL and therefore also less chance for typographical errors
PROC SUMMARY will compute the percent of 1 simply by asking for the MEAN (the mean of a 0/1 variable is the percent of ones)
Less typing to enter a 0 or 1 compared to 'N' or 'Y' and hence less chance for typographical errors
If a different question was asked, for example does any ID have more than 3 observations with 'Y', if you use 0/1s again PROC SUMMARY makes the solution easy, while otherwise you have to count the 'Y' values within an ID, which is certainly do-able, but not as easy as using PROC SUMMARY