topic Re: Filling up and Down Efficiently in SAS Programming

Filling up and Down Efficiently

Katie — Tue, 04 Feb 2014 19:48:00 GMT

Hello,

This is the data I have:

ID_VAR1 ID_VAR2 ID_VAR3 OTHERVAR_1 OTHERVAR_2 OTHERVAR_3

1 xxx 1x1 x . y

1 xxx 1x1 . z y

2 zzz 2x2 p u g

2 zzz 3x3 r f h

3 nnn 4x4 . q .

3 nnn 4x4 t q w

This is the data I would like:

ID_VAR1 ID_VAR2 ID_VAR3 OTHERVAR_1 OTHERVAR_2 OTHERVAR_3

1 xxx 1x1 x z y

2 zzz 2x2 p u g

2 zzz 3x3 r f h

3 nnn 4x4 t q w

So basically per unique combination of ID variables, I need to fill up and down other variables. I have quite a few variables to do this on and would like the most efficient solution.

Thanks!

Re: Filling up and Down Efficiently

data_null__ — Tue, 04 Feb 2014 20:01:43 GMT

Efficient to write or efficient to run?

Re: Filling up and Down Efficiently

Katie — Tue, 04 Feb 2014 20:04:24 GMT

I would prefer something that is efficient to write. As of right now I can only think of doing it variable by variable. When I would rather have some code cycle through all the variables in one step.

Re: Filling up and Down Efficiently

data_null__ — Wed, 22 Mar 2017 17:50:11 GMT

Efficient to write.

data un;
   input (ID_VAR1-ID_VAR3)(:1. :$3. :$3.) (OTHERVAR_1-OTHERVAR_3)(:$1.);
   cards; 
1 xxx 1x1 x . y
1 xxx 1x1 . z y
2 zzz 2x2 p u g
2 zzz 3x3 r f h
3 nnn 4x4 . q .
3 nnn 4x4 t q w
;;;;
   run; 
proc print; 
   run; 
data down;
   update un(obs=0) un;
   by ID_:;
   array f first:;
   if f[dim(f)] then index=0; 
   index+1; 
   output; 
   run; 
proc sort data=down;
   key id_:;
   key index / descending; 
   run; 
data up;
   update down(obs=0) down;
   by id_:;
   output; 
   run; 
proc sort data=up;
   key id_: index;
   run; 
proc print; 
   run;

Re: Filling up and Down Efficiently

data_null__ — Fri, 07 Feb 2014 20:37:29 GMT

I should add that using an array of first variables will not work if there are other variables in the data that have first as the first part of there names. This can be avoided with the addion of validvarname=any as long as there are no names that begin with FIRST. (first dot). For example.

data un;
   input (ID_VAR1-ID_VAR3)(:1. :$3. :$3.) (firstvar_1-firstvar_3)(:$1.);
   cards; 
1 xxx 1x1 x . y
1 xxx 1x1 . z y
2 zzz 2x2 p u g
2 zzz 3x3 r f h
3 nnn 4x4 . q .
3 nnn 4x4 t q w
;;;;
   run; 
proc print; 
   run; 
options validvarname=any;
data down;
   update un(obs=0) un;
   by ID_:;
   array f 
'first.'n:;
   if f[dim(f)] then index=0; 
   index+1; 
   output; 
   run;

options validvarname=v7;

proc sort data=down;

   key id_:;
   key index / descending; 
   run; 
data up;
   update down(obs=0) down;
   by id_:;
   output; 
   run; 
proc sort data=up;
   key id_: index;
   run; 
proc print; 
   run; 

Re: Filling up and Down Efficiently

Peter_C — Sat, 08 Feb 2014 08:23:15 GMT

thank you

That's a really neat way of generalising code to identify the last of (undefined) number of names in a list.

It's not hard to accept the restriction, reserving names beginning "first" for the special by-group indicators, without even needing validvarname=any

Peter

Re: Filling up and Down Efficiently

Katie — Thu, 17 Apr 2014 16:02:28 GMT

I am trying to adapt the code you have previously shown me to only fill down a couple of variables, however I cannot figure out how to make it work. Can you help?

This is what I have so far:

data down;

update try(obs=0) try;

by CPI;

array f[2] Race Gender first:;

if f[dim(f)] then index = 0;

indext+1;

ouput;

run;

The rest of the code I can do, it is just setting up the array to only update those two variables.

Thanks!

Katie

Re: Filling up and Down Efficiently

data_null__ — Thu, 17 Apr 2014 17:40:25 GMT

Consider this modification to my original program with the addition of othervar_4-othervar_6 specifically the CALL MISSING after the output statement. This effectively turns off the LOCF action for these variables.

data un;
   input (ID_VAR1-ID_VAR3)(:1. :$3. :$3.) (OTHERVAR_1-OTHERVAR_6)(:$1.);
   cards; 
1 xxx 1x1 x . y x . y
1 xxx 1x1 . z y . z y
2 zzz 2x2 p u g p u g
2 zzz 3x3 r f h r f h
3 nnn 4x4 . q . . q .
3 nnn 4x4 t q w t q w
;;;;
   run; 
proc print; 
   run; 
data down;
   update un(obs=0) un;
   by ID_:;
   array f first:;
   if f[dim(f)] then index=0; 
   index+1; 
   output; 
   call missing(of othervar_4-othervar_6);
   run; 
proc sort data=down;
   key id_:;
   key index / descending; 
   run; 
data up;
   update down(obs=0) down;
   by id_:;
   output; 
   call missing(of othervar_4-othervar_6);
   run; 
proc sort data=up;
   key id_: index;
   run; 
proc print; 
   run;

Re: Filling up and Down Efficiently

Katie — Thu, 17 Apr 2014 17:56:21 GMT

Thank you so much for your reply. What I'm finding is the variables in the Call statement are the ones NOT getting filled, while the others are. Is there a way to specify only the ones you want to be filled?

Re: Filling up and Down Efficiently

data_null__ — Thu, 17 Apr 2014 18:20:02 GMT

See if this is more suitable. You create two macro the BY variables and the LOCF variables and drop and keep them as follows. Notice the variables that are not keys or LOCF now come from SET statement.

data un;
   input (ID_VAR1-ID_VAR3)(:1. :$3. :$3.) (OTHERVAR_1-OTHERVAR_6)(:$1.);
   cards; 
1 xxx 1x1 x . y x . y
1 xxx 1x1 . z y . z y
2 zzz 2x2 p u g p u g
2 zzz 3x3 r f h r f h
3 nnn 4x4 . q . . q .
3 nnn 4x4 t q w t q w
;;;;
   run; 
proc print; 
   run; 
%let by=ID_:;
%let locf=othervar_1-othervar_3;
data down;
   update un(obs=0 keep=&by) un(keep=&by &locf);
   by ID_:;
   set un(drop=&by &locf);
   array f first:;
   if f[dim(f)] then index=0; 
   index+1; 
   output; 
   run; 
proc sort data=down;
   key id_:;
   key index / descending; 
   run; 
data up;
   update down(obs=0 keep=&by) down(keep=&by &locf);
   by id_:;
   set down(drop=&by &locf);
   output; 
   run; 
proc sort data=up;
   key id_: index;
   run; 
proc print; 
   run;

Re: Filling up and Down Efficiently

Ksharp — Fri, 18 Apr 2014 08:33:33 GMT

Null,

Why not DOW skill ?

data un;
   input (ID_VAR1-ID_VAR3)(:1. :$3. :$3.) (OTHERVAR_1-OTHERVAR_3)(:$1.);
   cards; 
1 xxx 1x1 x . y
1 xxx 1x1 . z y
2 zzz 2x2 p u g
2 zzz 3x3 r f h
3 nnn 4x4 . q .
3 nnn 4x4 t q w
;;;;
   run; 
data want;
 length a1 a2 a3 $ 10;
 do until(last.ID_VAR3);
  set un;
  by ID_VAR1-ID_VAR3;
  a1=coalescec(a1,OTHERVAR_1);
  a2=coalescec(a2,OTHERVAR_2);
  a3=coalescec(a3,OTHERVAR_3);
 end;
 do until(last.ID_VAR3);
  set un;
  by ID_VAR1-ID_VAR3;
  OTHERVAR_1=a1;
  OTHERVAR_2=a2;
  OTHERVAR_3=a3;
  output;
 end;
 drop a1 a2 a3;
run;

Xia Keshan

Re: Filling up and Down Efficiently

data_null__ — Fri, 18 Apr 2014 11:24:11 GMT

Because it doesn't work. Add some observations so that the data is more realistic and you will see.

Re: Filling up and Down Efficiently

Ksharp — Fri, 18 Apr 2014 11:29:22 GMT

Null,

With all due respect , I agree with you . OP should add some data to explain his question clearer .

Message was edited by: xia keshan

Re: Filling up and Down Efficiently

Haikuo — Fri, 18 Apr 2014 14:11:15 GMT

If you only have less or equal two rows per (ID_VAR1 ID_VAR2 ID_VAR3), then PROC SQL and some unconventional usage of Max() function could be put into your advantage here: (Raw input was stolen from _null_'s post)

data un;

input (ID_VAR1-ID_VAR3)(:1. :$3. :$3.) (OTHERVAR_1-OTHERVAR_6)(:$1.);

cards;

1 xxx 1x1 x . y x . y

1 xxx 1x1 . z y . z y

2 zzz 2x2 p u g p u g

2 zzz 3x3 r f h r f h

3 nnn 4x4 . q . . q .

3 nnn 4x4 t q w t q w

;;;;

run;

proc sql;

select CAT('COALESCEC(',name,',', 'MAX(',NAME,')',') AS ',NAME) into :names SEPARATED BY ' , ' from DICTIONARY.COLUMNS

WHERE LIBNAME='WORK'

AND MEMNAME='UN'

AND NAME NOT IN ('ID_VAR1' 'ID_VAR3' 'ID_VAR2')

;QUIT;

proc sql;

create table want as

select ID_VAR1, ID_VAR2, ID_VAR3 ,&NAMES FROM UN

GROUP BY ID_VAR1, ID_VAR2, ID_VAR3

;

QUIT;

Haikuo

Re: Filling up and Down Efficiently

Katie — Fri, 18 Apr 2014 14:26:04 GMT

Here is an example of what my data looks like:

Data Have -

CPI EnrollDate VisitDate BP1 Race Gender

123 1/1/2001 1/1/2001 120/80 White Female

123 1/1/2001 2/1/2001 119/85

123 9/9/2009 9/9/2009 115/75 White

123 9/9/2009 10/9/2009

456 2/2/2002 2/2/2002 118/85 Black Male

456 2/2/2002 3/2/2002 110/82 Black Male

789 3/3/2003 3/3/2003 135/81 Hispanic

789 3/3/2003 4/3/2003 132/83 Hispanic Male

789 3/3/2003 5/3/2003 Male

789 3/3/2003 6/3/2003 128/81

Data Want -

CPI EnrollDate VisitDate BP1 Race Gender

123 1/1/2001 1/1/2001 120/80 White Female

123 1/1/2001 2/1/2001 119/85 White Female

123 9/9/2009 9/9/2009 115/75 White Female

123 9/9/2009 10/9/2009 White Female

456 2/2/2002 2/2/2002 118/85 Black Male

456 2/2/2002 3/2/2002 110/82 Black Male

789 3/3/2003 3/3/2003 135/81 Hispanic Male

789 3/3/2003 4/3/2003 132/83 Hispanic Male

789 3/3/2003 5/3/2003 Hispanic Male

789 3/3/2003 6/3/2003 128/81 Hispanic Male

Re: Filling up and Down Efficiently

Haikuo — Fri, 18 Apr 2014 15:02:11 GMT

it seems to me that you tried to filled up missing demographic info for patient, and for each patient it can only one value if not missing. For instance, a patient (same CPI) can not be both male or female at the same time. therefore my simple Proc SQL approach should have a shot:

DATA HAVE;

INPUT CPI :$3. (EnrollDate VisitDate) (:MMDDYY10.) (BP1 Race Gender) (:$10.);

CARDS;

123 1/1/2001 1/1/2001 120/80 White Female

123 1/1/2001 2/1/2001 119/85 . .

123 9/9/2009 9/9/2009 115/75 White .

123 9/9/2009 10/9/2009 . . .

456 2/2/2002 2/2/2002 118/85 Black Male

456 2/2/2002 3/2/2002 110/82 Black Male

789 3/3/2003 3/3/2003 135/81 Hispanic .

789 3/3/2003 4/3/2003 132/83 Hispanic Male

789 3/3/2003 5/3/2003 . . Male

789 3/3/2003 6/3/2003 128/81 . .

;

proc sql;

create table want as

select CPI, EnrollDate FORMAT=MMDDYY10., VisitDate FORMAT=MMDDYY10., BP1,

COALESCEC(RACE, MAX(RACE)) AS RACE,

COALESCEC(GENDER, MAX(GENDER)) AS GENDER

FROM HAVE

GROUP BY CPI

;

QUIT;

Note: coalescec() is abundant in your case, Max() alone is sufficient .

Haikuo

Re: Filling up and Down Efficiently

data_null__ — Fri, 18 Apr 2014 15:09:45 GMT

For demographic variables I would probably create a DM data with one obs per CPI and not LOCF those variables. Here is UPDATE method LOCF and NOCB(next obs carried back?). This method has the advantage of LOCFing a lot of variables without a lot of code. There is also the assumption that any completely missing visits have been added to the data.

data un;
   infile cards firstobs=2 missover; 
   input CPI :$3. (EnrollDate VisitDate)(:mmddyy.) (BP1 Race Gender)($);
   format Enr: Vis: date9.; 
   cards; 
CPI EnrollDate VisitDate BP1    Race     Gender
123 1/1/2001   1/1/2001  120/80  White     Female
123 1/1/2001   2/1/2001  119/85 
123 9/9/2009   9/9/2009  115/75  White
123 9/9/2009   10/9/2009 
456 2/2/2002   2/2/2002  118/85  Black      Male
456 2/2/2002   3/2/2002  110/82  Black      Male
789 3/3/2003   3/3/2003  135/81  Hispanic
789 3/3/2003   4/3/2003  132/83  Hispanic   Male
789 3/3/2003   5/3/2003  .       .    Male
789 3/3/2003   6/3/2003  128/81 
;;;;
   run; 
proc print; 
   run; 
%let by=CPI;
%let locf=BP1 Race Gender;;
data down;
   update un(obs=0 keep=&by) un(keep=&by &locf);
   by &by;
   set un(drop=&by &locf);
   array f first:;
   if f[dim(f)] then index=0; 
   index+1; 
   output; 
   run; 
proc sort data=down;
   key &by;
   key index / descending; 
   run; 
data up;
   if 0 then set un;
   update down(obs=0 keep=&by) down(keep=&by &locf);
   by &by;
   set down(drop=&by &locf);
   output; 
   run; 
proc sort data=up;
   key &by index;
   run; 
proc print; 
   run;

Re: Filling up and Down Efficiently

art297 — Fri, 18 Apr 2014 15:22:22 GMT

Katie,

Here is an alternative approach that you might want to try:

data have;

input CPI (EnrollDate VisitDate) (:mmddyy10.) (BP1 Race Gender) ($);

cards;

123 1/1/2001 1/1/2001 120/80 White Female

123 1/1/2001 2/1/2001 119/85 . .

123 9/9/2009 9/9/2009 115/75 White .

123 9/9/2009 10/9/2009 . . .

456 2/2/2002 2/2/2002 118/85 Black Male

456 2/2/2002 3/2/2002 110/82 Black Male

789 3/3/2003 3/3/2003 135/81 Hispanic .

789 3/3/2003 4/3/2003 132/83 Hispanic Male

789 3/3/2003 5/3/2003 . . Male

789 3/3/2003 6/3/2003 128/81 . .

;

data want (drop=_:);

length _race _gender $8;

do until (last.cpi);

set have;

by cpi;

race=coalescec(race,_race);

gender=coalescec(gender,_gender);

_race=race;

_gender=gender;

end;

do until (last.cpi);

set have (drop=race gender);

by cpi;

output;

end;

run;

Re: Filling up and Down Efficiently

Katie — Fri, 18 Apr 2014 15:31:18 GMT

Thank you all so much for your help. I believe I've gotten it to work!

Thanks again!

Re: Filling up and Down Efficiently

Everything — Wed, 22 Mar 2017 12:54:31 GMT

data _null_,

Can you explain, please, syntax of operation array f first:; ?

I mean for what first:; stands?