Re: Populating columns with missing variables

RandyStan · Posted 06-30-2018 06:16 PM

Dear All My data is as follows Date ST IDa IDb Cat JUN012018 500 A AA I JUN022018 500 A AA I JUN032018 500 A AA I JUN012018 501 A AA JUN022018 501 A AA JUN032018 501 A AA JUN012018 502 A AA I JUN012018 502 A AA I JUN012018 500 A AAB JUN022018 500 A AAB JUN032018 500 A AAB JUN012018 501 A AAB D JUN022018 501 A AAB D JUN032018 501 A AAB D JUN012018 502 A AAB JUN012018 502 A AAB and so on The data set should look like this Date ST IDa IDb Cat JUN012018 500 A AA I JUN022018 500 A AA I JUN032018 500 A AA I JUN012018 501 A AA I JUN022018 501 A AA I JUN032018 501 A AA I JUN012018 502 A AA I JUN012018 502 A AA I JUN012018 500 A AAB D JUN022018 500 A AAB D JUN032018 500 A AAB D JUN012018 501 A AAB D JUN022018 501 A AAB D JUN032018 501 A AAB D JUN012018 502 A AAB D JUN012018 502 A AAB D Thanks Stan

RandyStan · Posted 06-30-2018 06:22 PM

Putting this question again because the formatting seems to be messed up

Dear All:
My data set is as follows

Date                            ST                        IDa                         IDb              Cat
JUN012018               500                          A                           AA                I
JUN022018               500                          A                           AA                I
JUN032018               500                          A                           AA                I

JUN012018                501                         A                           AA 

JUN022018               501                          A                           AA

JUN032018                501                         A                           AA

JUN012018                502                         A                           AA                 I

JUN012018                502                          A                           AA                I

JUN012018               500                          A                           AAB               
JUN022018               500                          A                           AAB                
JUN032018               500                          A                           AAB                

JUN012018                501                         A                           AAB              D

JUN022018               501                          A                           AAB              D

JUN032018                501                         A                           AAB              D

JUN012018                502                         A                           AAB                 

JUN012018                502                          A                           AAB                

 

 

and so on

The data set should look like this

 

 

Date                            ST                        IDa                         IDb              Cat
JUN012018               500                          A                           AA                I
JUN022018               500                          A                           AA                I
JUN032018               500                          A                           AA                I

JUN012018                501                         A                           AA                I

JUN022018               501                          A                           AA                I

JUN032018                501                         A                           AA                I

JUN012018                502                         A                           AA                 I

JUN012018                502                          A                           AA                I

JUN012018               500                          A                           AAB             D              
JUN022018               500                          A                           AAB             D            
JUN032018               500                          A                           AAB             D   

JUN012018                501                         A                           AAB              D

JUN022018               501                          A                           AAB              D

JUN032018                501                         A                           AAB              D

JUN012018                502                         A                           AAB              D      

JUN012018                502                          A                           AAB             D

 

 

Thanks 

Stan

pau13rown · Posted 06-30-2018 06:51 PM

hat's the difference between the two?

RandyStan · Posted 06-30-2018 07:14 PM

No difference. I saw that the formatting was messed up, so I posted it again. OI hope that the data and the question is clearer now.

Reeza · Posted 06-30-2018 07:25 PM

The start and end look the same to me.

Here’s some instructions on posting sample data. Also, don’t edit the info in the code block in the main editor, that’s messes up the line breaks for some reason.

@RandyStan wrote:

No difference. I saw that the formatting was messed up, so I posted it again. OI hope that the data and the question is clearer now.

RandyStan · Posted 06-30-2018 07:30 PM

Please check the CAT column. In the first data set the variables are missing. In the second, the data set I want, they column CAT is populated.

Thanks so much

Reeza · Posted 06-30-2018 07:33 PM

Given the way the data is formatted it’s really hard to see that or what the logic is.

@RandyStan wrote:

Please check the CAT column. In the first data set the variables are missing. In the second, the data set I want, they column CAT is populated.

Thanks so much

Patrick · Posted 06-30-2018 09:52 PM

@RandyStan

Below picture created by combining your have and want data.

Please explain the logic how you'd populate "CAT_WANT".

Below how you'd best post sample data if you want to motivate people to post answers with working code.

data have;
  infile datalines dlm=' ' truncover;
  format Date date9.;
  input _dt1 $1-3 _dt2 $4-5 _dt3 $6-9 (ST IDa IDb Cat_Have) ($);
  date=input(cats(_dt2,_dt1,_dt3),date9.);
  drop _dt:;
  datalines;
JUN012018 500 A AA I
JUN022018 500 A AA I
JUN032018 500 A AA I
JUN012018 501 A AA 
JUN022018 501 A AA
JUN032018 501 A AA
JUN012018 502 A AA I
JUN012018 502 A AA I
JUN012018 500 A AAB 
JUN022018 500 A AAB 
JUN032018 500 A AAB 
JUN012018 501 A AAB D
JUN022018 501 A AAB D
JUN032018 501 A AAB D
JUN012018 502 A AAB 
JUN012018 502 A AAB 
;
run;

PGStats · Posted 06-30-2018 10:50 PM

Use a double DO UNTIL() loop.The first loop records the last non-missing Cat value in the IDb-group, the second loop forces that value where Cat is missing :

data have;
length Date $10 ST 8 IDa $1 IDb $3 Cat $1;
infile datalines truncover;
input Date ST IDa IDb Cat;
datalines;
JUN012018 500 A AA I
JUN022018 500 A AA I
JUN032018 500 A AA I
JUN012018 501 A AA 
JUN022018 501 A AA
JUN032018 501 A AA
JUN012018 502 A AA I
JUN012018 502 A AA I
JUN012018 500 A AAB 
JUN022018 500 A AAB 
JUN032018 500 A AAB 
JUN012018 501 A AAB D
JUN022018 501 A AAB D
JUN032018 501 A AAB D
JUN012018 502 A AAB 
JUN012018 502 A AAB 
;

data want;
do until(last.IDb);
    set have; by IDb notsorted;
    if not missing(Cat) then groupCat = Cat;
    end;
do until(last.IDb);
    set have; by IDb notsorted;
    if missing(Cat) then Cat = groupCat;
    output;
    end;
drop groupCat;
run;

proc print data=want noobs; run;

PG

mkeintz · Posted 07-01-2018 01:22 AM

This problem is very amenable to a self merge, where the first instance of HAVE has all the records and variables. The second instance has only the BY-variable(s) (IDB in your case) and the new variable of interest (cat_want, renamed from cat_have), and it only has records with non-blank values of cat_want:

data want;
  merge have
        have (keep=idb cat_have rename=(cat_have=cat_want) where=(cat_want^=' '));
  by idb;
run;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

novinosrin · Posted 06-30-2018 11:57 PM

data have;
length Date $10 ST 8 IDa $1 IDb $3 Cat $1;
infile datalines truncover;
input Date ST IDa IDb Cat;
datalines;
JUN012018 500 A AA I
JUN022018 500 A AA I
JUN032018 500 A AA I
JUN012018 501 A AA 
JUN022018 501 A AA
JUN032018 501 A AA
JUN012018 502 A AA I
JUN012018 502 A AA I
JUN012018 500 A AAB 
JUN022018 500 A AAB 
JUN032018 500 A AAB 
JUN012018 501 A AAB D
JUN022018 501 A AAB D
JUN032018 501 A AAB D
JUN012018 502 A AAB 
JUN012018 502 A AAB 
;


proc sql;
create table want(drop=cat) as
select *,max(cat) as cat1
from have
group by idb
order by idb,st,date;
quit;

novinosrin · Posted 07-01-2018 12:53 AM

data have;
infile datalines truncover;
input Date : $10. ST  IDa $ IDb $ Cat $;
datalines;
JUN012018 500 A AA I
JUN022018 500 A AA I
JUN032018 500 A AA I
JUN012018 501 A AA 
JUN022018 501 A AA
JUN032018 501 A AA
JUN012018 502 A AA I
JUN012018 502 A AA I
JUN012018 500 A AAB 
JUN022018 500 A AAB 
JUN032018 500 A AAB 
JUN012018 501 A AAB D
JUN022018 501 A AAB D
JUN032018 501 A AAB D
JUN012018 502 A AAB 
JUN012018 502 A AAB 
;

data want;
set have;
if (_n_ = 1) then do;
if 0 then set have(keep=idb cat);
    declare hash h(dataset: "have(keep=idb cat where=(not missing(cat))", duplicate: "r");
    h.definekey('idb');
    h.definedata('cat');
    h.definedone();
 end;
 rc=h.find();
 drop rc;
 run;

Classroom Training Available!