topic Re: Add observations to data in SAS Programming

Add observations to data

mkt_apprentice — Wed, 27 Mar 2019 17:48:48 GMT

Hello SAS communities,

I have a data set like this:

id day drinks

1 2 1

1 3 3

1 5 4

2 3 1

3 2 1

3 4 2

The study lasts 5 days, and days a person doesn't have any drink (drink = 0) are not reported in the data. How could I create these observations (drink = 0 in the days the person doesn't drink) in this data set? The data I want looks like this:

id day drinks

1 1 0

1 2 1

1 3 3

1 4 0

1 5 4

2 1 0

2 2 0

2 3 1

2 4 0

2 5 0

3 1 0

3 2 1

3 3 0

3 4 2

3 5 0

Thank you!

Re: Add observations to data

novinosrin — Wed, 27 Mar 2019 17:55:44 GMT

data have;
input id   day  drinks;
cards;
1     2      1
1     3      3
1     5      4
2     3      1
3     2      1
3     4      2
;

data want;
if _n_=1 then do;
dcl hash H (dataset:'have') ;
   h.definekey  ("id",'day') ;
   h.definedata ("drinks") ;
   h.definedone () ;
end;
set have(keep=id);;
by id;/*assuming it's sorted by id as your sample suggests*/
if first.id;
do day=1 to 5;
rc=h.find();
if rc ne 0 then drinks=0;
output;
end;
drop rc;
run;

Re: Add observations to data

mkt_apprentice — Wed, 27 Mar 2019 17:57:10 GMT

Thanks a lot! I will try and update the results if it works.

Re: Add observations to data

mkt_apprentice — Wed, 27 Mar 2019 18:27:13 GMT

My data actually has more variables in addition to these 3 variables. What if I want to keep all of the rest variables together with the three variables when creating these new observations?

Re: Add observations to data

mkt_apprentice — Wed, 27 Mar 2019 18:29:41 GMT

The code you wrote works. However, it only keeps the 3 variables (as I posted a simplified version of my data). The other variables are just demographics and do not change in different days. How could I keep all of them when I create new observations = 0?

Thank you

Re: Add observations to data

novinosrin — Wed, 27 Mar 2019 19:13:20 GMT

Hi @mkt_apprentice

Include you other variables in definedata like

h.definedata ("drinks","var1","var2"....and so on up to "varn") ;

Also add this statement

do day=1 to 5;
rc=h.find();
if rc ne 0 then do; 
drinks=0;
call missing(var1,var2, .....upto varn);
output;
end;

Also please post a more representative sample so that we can avoid going back and forth.

Re: Add observations to data

mkt_apprentice — Wed, 27 Mar 2019 20:23:26 GMT

Thanks. Can you rewrite the whole code in one block? I added the part you suggested and the variables to keep the old ones. SAS returns that There was 1 unclosed DO block.

Could you please help?

Re: Add observations to data

novinosrin — Wed, 27 Mar 2019 20:40:36 GMT

Hi @mkt_apprentice Please review the revised sample below. You will get it

/*Added additional variables to the sample HAVE*/
data have;
input id   day  drinks ;
blah=rand('integer',1,100);
blahblah=rand('integer',1,100);
blahblahblah=rand('integer',1,100);
cards;
1     2      1
1     3      3
1     5      4
2     3      1
3     2      1
3     4      2
;

data want;
if _n_=1 then do;
dcl hash H (dataset:'have') ;
   h.definekey  ("id",'day') ;
   h.definedata ("drinks",'blah','blahblah','blahblahblah') ;
   h.definedone () ;
end;
set have(keep=id);;
by id;/*assuming it's sorted by id as your sample suggests*/
if first.id;
do day=1 to 5;
rc=h.find();
if rc ne 0 then do; 
drinks=0;
call missing(blah,blahblah,blahblahblah);
end;
output;
end;
drop rc;
run;

Re: Add observations to data

andreas_lds — Wed, 27 Mar 2019 20:54:51 GMT

Thanks @novinosrin for providing data in usable form.

Another way to solve the issue:

data want;
    set have;
    by id;
    
    length expectedDay backupDay backupDrinks 8;
    retain expectedDay;
    drop expectedDay backupDay backupDrinks;
    
    if first.id then do;
        expectedDay = 1;
    end;
    
    if day ^= expectedDay then do;
        backupDay = day;
        backupDrinks = drinks;
        drinks = 0;
        
        do day = expectedDay to backupDay-1;
            output;
        end;
        
        drinks = backupDrinks;
    end;
    
    output;
    
    expectedDay = day + 1;
    
    if last.id and day ^= 5 then do;
        drinks = 0;
        do day = day+1 to 5;
            output;
        end;
    end;
    
run;

Re: Add observations to data

mkt_apprentice — Wed, 27 Mar 2019 20:59:56 GMT

Thanks a lot for the code. Just one more thing that I need your help. The demographic variables that are associated each person (id) are not kept for the observation drinks = 0. They all become "." or missing data. Is there any way I can keep them in the newly created drinks=0 observations? I copied your code and added the demographic variables to the appropriate location in the code but the results include . rather than these demographic variables. Can you please help?

Re: Add observations to data

novinosrin — Wed, 27 Mar 2019 21:03:48 GMT

Comment out this part

/*call missing(blah,blahblah,blahblahblah);*/

Complete code

/*Added additional variables to the sample HAVE*/
data have;
input id   day  drinks ;
blah=rand('integer',1,100);
blahblah=rand('integer',1,100);
blahblahblah=rand('integer',1,100);
cards;
1     2      1
1     3      3
1     5      4
2     3      1
3     2      1
3     4      2
;

data want;
if _n_=1 then do;
if 0 then set have;
dcl hash H (dataset:'have') ;
   h.definekey  ("id",'day') ;
   h.definedata ("drinks",'blah','blahblah','blahblahblah') ;
   h.definedone () ;
end;
set have(keep=id);;
by id;/*assuming it's sorted by id as your sample suggests*/
if first.id;
do day=1 to 5;
rc=h.find();
if rc ne 0 then do; 
drinks=0;
/*call missing(blah,blahblah,blahblahblah);*/
end;
output;
end;
drop rc;
run;

Re: Add observations to data

mkt_apprentice — Wed, 27 Mar 2019 21:04:13 GMT

What does call missing do? I want these demographic variables to be the same as the ones in observations drinks differ than 0.

For example:

id day drinks age gender

1 1 2 24 1

1 2 0 24 1

Currently the resulting data is like this:

id day drinks age gender

1 1 2 24 1

1 2 0 . .

Re: Add observations to data

novinosrin — Wed, 27 Mar 2019 21:04:21 GMT

@mkt_apprentice Edited the previous with complete code

Re: Add observations to data

mkt_apprentice — Wed, 27 Mar 2019 21:09:50 GMT

It still produces . values 😞

Re: Add observations to data

novinosrin — Wed, 27 Mar 2019 21:09:58 GMT

Hi @mkt_apprentice

I owe an apology as I overlooked a minor issue. Really sorry

Please find the corrected code below

/*Added additional variables to the sample HAVE*/
data have;
input id   day  drinks ;
blah=rand('integer',1,100);
blahblah=rand('integer',1,100);
blahblahblah=rand('integer',1,100);
cards;
1     2      1
1     3      3
1     5      4
2     3      1
3     2      1
3     4      2
;

data want;
if _n_=1 then do;
if 0 then set have;
dcl hash H (dataset:'have',multidata:'y') ;
   h.definekey  ("id",'day') ;
   h.definedata ("drinks") ;
   h.definedone () ;
end;
set have(keep=id blah blahblah blahblahblah);
by id;/*assuming it's sorted by id as your sample suggests*/
if first.id;
do day=1 to 5;
rc=h.find();
if rc ne 0 then drinks=0;
output;
end;
drop rc;
run;

Re: Add observations to data

mkt_apprentice — Wed, 27 Mar 2019 21:12:36 GMT

What do you mean in multidata: 'y'? Do I need to create a separate data set for the demographic variables?

Re: Add observations to data

novinosrin — Wed, 27 Mar 2019 21:13:38 GMT

No, just ignore that option. Not needed. Will work without it

/*Added additional variables to the sample HAVE*/
data have;
input id   day  drinks ;
blah=rand('integer',1,100);
blahblah=rand('integer',1,100);
blahblahblah=rand('integer',1,100);
cards;
1     2      1
1     3      3
1     5      4
2     3      1
3     2      1
3     4      2
;

data want;
if _n_=1 then do;
if 0 then set have;
dcl hash H (dataset:'have') ;
   h.definekey  ("id",'day') ;
   h.definedata ("drinks") ;
   h.definedone () ;
end;
set have(keep=id blah blahblah blahblahblah);
by id;/*assuming it's sorted by id as your sample suggests*/
if first.id;
do day=1 to 5;
rc=h.find();
if rc ne 0 then drinks=0;
output;
end;
drop rc;
run;

Re: Add observations to data

mkt_apprentice — Wed, 27 Mar 2019 21:20:43 GMT

A BIG THANKS TO YOU!!!

Re: Add observations to data

novinosrin — Wed, 27 Mar 2019 21:21:08 GMT

You are welcome! have a good one!