topic Losing data while combining 2 variables into one in SAS Programming

Losing data while combining 2 variables into one

Mscarboncopy — Wed, 09 Jun 2021 17:39:09 GMT

I need to make the keys from var B become the continuation of var A

The original var A has values from 1-22 and once I rename B into A it would go up to 51 (as you can see in the code below).

My code works except it looks like I am losing only the first prep var so I might need only one statement somewhere to prevent it from dropping that first one?

Please see the data example after the code.

Data want;
set have;

if B = 1 then prepvar = 23 ;

else if B = 2 then prepvar = 24 ;

else if B = 3 then prepvar = 25 ;

else if B = 4 then prepvar = 26 ;

else if B = 5 then prepvar = 27 ;

else if B = 6 then prepvar = 28 ;

else if B = 7 then prepvar = 29 ;

else if B = 8 then prepvar = 30 ;

else if B = 9 then prepvar = 31 ;

else if B = 10 then prepvar = 32 ;

else if B = 11 then prepvar = 33 ;

else if B = 12 then prepvar = 34 ;

else if B = 13 then prepvar = 35 ;

else if B = 14 then prepvar = 36 ;

else if B = 15 then prepvar = 37 ;

else if B = 16 then prepvar = 38 ;

else if B = 17 then prepvar = 39 ;

else if B = 18 then prepvar = 40 ;

else if B = 19 then prepvar = 41 ;

else if B = 20 then prepvar = 42 ;

else if B = 21 then prepvar = 43 ;

else if B = 22 then prepvar = 44 ;

else if B = 23 then prepvar = 45 ;

else if B = 24 then prepvar = 46 ;

else if B = 25 then prepvar = 47 ;

else if B = 26 then prepvar = 48 ;

else if B = 27 then prepvar = 49 ;

else if B = 28 then prepvar = 50 ;

else if B = 29 then prepvar = 51 ;

if 1<=A<=22 then A = A; *keeping the var as is for the original data that has values that go from 1-2;

else A= prepvar; * adding the new values from var B;

Run;

Example of what I am getting. it looks like it is dropping the first prepvar value only. I am not sure why and how to prevent this from happening.

ID VAR A VAR B prepvar

Thanks,

Re: Losing data while combining 2 variables into one

Reeza — Wed, 09 Jun 2021 17:51:21 GMT

It's hard to see what you're doing versus what you want.

Can you show us what you start with exactly and what you want to see as the end results.

You code can be simplified significantly, but if it's not working, that may not be very helpful.

Right now your logic is :

If Variable B is between the values of 1 and 29 create a new variable PrepVar which adds 22 to the value of B.
If A is not between 1 and 22 then re-assign the value of prepvar to the variable A

data want;
set have;

if B>=1  and B<=29 then prepVar = B+22;

*If VarA not a value in 1 to 22, replace it with prepvar;
if  not (A >=1 and A <= 22) then A = prepvar;

run;

@Mscarboncopy wrote:

I need to make the keys from var B become the continuation of var A

The original var A   has values from 1-22 and once I rename B into A it would go up to 51 (as you can see in the code below).

My code works except it looks like I am losing only the first prep var so I might need only one statement somewhere to prevent it from dropping that first one?

Please see the data example after the code.

Data want;
set have;

if B = 1 then prepvar = 23 ;

else if B = 2 then prepvar = 24 ;

else if B = 3 then prepvar = 25 ;

else if B = 4 then prepvar = 26 ;

else if B = 5 then prepvar = 27 ;

else if B = 6 then prepvar = 28 ;

else if B = 7 then prepvar = 29 ;

else if B = 8 then prepvar = 30 ;

else if B = 9 then prepvar = 31 ;

else if B = 10 then prepvar = 32 ;

else if B = 11 then prepvar = 33 ;

else if B = 12 then prepvar = 34 ;

else if B = 13 then prepvar = 35 ;

else if B = 14 then prepvar = 36 ;

else if B = 15 then prepvar = 37 ;

else if B = 16 then prepvar = 38 ;

else if B = 17 then prepvar = 39 ;

else if B = 18 then prepvar = 40 ;

else if B = 19 then prepvar = 41 ;

else if B = 20 then prepvar = 42 ;

else if B = 21 then prepvar = 43 ;

else if B = 22 then prepvar = 44 ;

else if B = 23 then prepvar = 45 ;

else if B = 24 then prepvar = 46 ;

else if B = 25 then prepvar = 47 ;

else if B = 26 then prepvar = 48 ;

else if B = 27 then prepvar = 49 ;

else if B = 28 then prepvar = 50 ;

else if B = 29 then prepvar = 51 ;

if 1<=A<=22 then A = A; *keeping the var as is for the original data that has values that go from 1-2;
else A= prepvar; * adding the new values from var B;

Run;

Example of what I am getting. it looks like it is dropping the first prepvar value only. I am not sure why and how to prevent this from happening.

ID                            VAR A               VAR B   prepvar

Thanks,

Re: Losing data while combining 2 variables into one

Mscarboncopy — Wed, 09 Jun 2021 18:38:39 GMT

Thank you. This is a much more elegant way to do what I was doing. It is still dropping the first prep var though.

Re: Losing data while combining 2 variables into one

Reeza — Wed, 09 Jun 2021 19:08:51 GMT

It's hard to see what you're doing versus what you want.

Can you show us what you start with exactly and what you want to see as the end results. Since your code is not working I have no idea what you mean by dropping the first prep_var. Please be more specific.

Re: Losing data while combining 2 variables into one

Mscarboncopy — Wed, 09 Jun 2021 19:31:53 GMT

I don't know what else to show. It is highlighted in yellow in the data example I gave. The example is showing the 2 vars A and B and prep. I am not doing anything else other than trying to combine A and B using the prep var. And somehow, for each id, the combining is dropping the first prep var. It works for all the others but never the first one. Those are simply dropped. I should have 9 entries for ID A for example and I end up with only 8 because the first prep var (in this case 27) disappears. It is not in VAR A, as you can see. Thank you.

Re: Losing data while combining 2 variables into one

Reeza — Wed, 09 Jun 2021 19:35:12 GMT

I should have 9 entries for ID A for example and I end up with only 8 because the first prep var (in this case 27) disappears. It is not in VAR A, as you can see.

Actually I can't see what's not there and I don't even know you want it there.
How can I know you wanted to add rows - this is the first time you've mentioned that?

Please show your input.
Please show what you expect as the output, preferably that aligns with the output. For example, it should have the missing row you mentioned.

Re: Losing data while combining 2 variables into one

Mscarboncopy — Wed, 09 Jun 2021 19:58:38 GMT

Oh I see. Sorry about that. This is the input for A:

VAR A VAR B Prep

A 6 5 27

A . 6 28

A . 11 33

A . 12 34

A . 22 44

A . 24 46

A . 25 47

A . 29 51

Re: Losing data while combining 2 variables into one

Reeza — Wed, 09 Jun 2021 20:01:59 GMT

And the expected output please?

Re: Losing data while combining 2 variables into one

Mscarboncopy — Wed, 09 Jun 2021 20:08:06 GMT

ID VAR A

A         6
A        27
A        28
A        33
A        34
A        44
A        46
A        47
A        51

Re: Losing data while combining 2 variables into one

Reeza — Wed, 09 Jun 2021 20:21:22 GMT

This works for what you've posted:

data have;
infile cards dsd;
input ID $    A B     Prep;
cards;
A,          6,                   5 ,           27
A,          . ,                   6 ,           28
A,          . ,                   11 ,         33
A,          . ,                   12  ,        34
A,          . ,                   22  ,        44
A,          . ,                   24  ,        46
A,          . ,                   25  ,        47
A,          . ,                   29  ,        51
;;;;
run;

data want;
set have (in=t1 keep=  ID A where=(not missing(A))) 
    have(in=t2 drop = A rename= B=A);
if t2 then A = A + 22;  
run;

Re: Losing data while combining 2 variables into one

Mscarboncopy — Thu, 10 Jun 2021 12:54:09 GMT

That is it. Thank you !!