topic Re: Impute missing data in SAS Programming

data

Emma8 — Wed, 15 Jul 2020 13:41:57 GMT

Hi.

Re: Impute missing data

Kurt_Bremser — Mon, 13 Jul 2020 20:42:02 GMT

Please post SAS datasets in SAS code (data step with datalines). Keep in mind that this is not an Excel support forum.

Re: Impute missing data

ballardw — Mon, 13 Jul 2020 21:04:38 GMT

One basic program:

Proc sql;
   create table names as
   select distinct name
   from have
   ;
quit;

data want;
   set names;
   do date = "28FEB2020"D to"03MAR2020"D;;
      output;
   end;
   format date date9.;
run;

proc sort data=want;
   by date name;
run;

Though I probably wouldn't use that sort order personally.

If you really must use macro variables for the start and end it should be obvious where they go.

I would strongly recommend using a two digit day of the month so there is no doubt whether 3Mar was actually intended to be 30Mar.

Re: Impute missing data

Emma8 — Tue, 14 Jul 2020 00:15:05 GMT

Please find the Simplified data in below text box

Re: Impute missing data

ballardw — Mon, 13 Jul 2020 21:58:16 GMT

I refuse to generate anything with 2 digit years and really dislike one digit days of months. Formats are transient. You can use any format you want. ddmmyy6. instead of date9.

If the order of the names is hyper critical then you better specify that as a requirement.

data

Emma8 — Wed, 15 Jul 2020 13:43:29 GMT

Re: Impute missing data

ballardw — Mon, 13 Jul 2020 22:48:57 GMT

Create a second set with the start end periods as I did and append to your existing data.

I think.

Not seeing a clearly stated difference.

Re: Impute missing data

LeonidBatkhan — Tue, 14 Jul 2020 01:17:45 GMT

Hi Emma8,

First of all, your first data step HAVE is not correct, it does not read your datalines. Second of all, if you are producing DATEs defined by macro variables start and end, those DATEs defined in the first data step are irrelevant, you need to generate your new DATEs.

Here is the code I believe will do what you want:

%let start="28FEB2020"D;
%let end="3MAR2020"D;

data HAVE;
   length NAME $10;
   infile datalines delimiter=':';
   input NAME;
   datalines;
Anna, Josh: 28/02/2020
John: 28/02/2020
Anna, Josh: 29/02/2020
John: 29/02/2020
Kate: 29/02/2020
Kate: 29/02/2020
John: 01/03/2020
Kate: 01/03/2020
;

proc sort data=HAVE out=UNIQUE_NAMES(keep=NAME) nodupkey;
   by NAME;
run;

data WANT;
   set UNIQUE_NAMES;
   do DATE=&start to &end;
      output;
   end;
   format DATE ddmmyy10.;
run;

proc sort data=WANT;
   by DATE;
run;

Hope this helps.

Re: Impute missing data

Emma8 — Tue, 14 Jul 2020 01:23:04 GMT

Wow that was really helpful and works exactly what I wanted. Thank you so much!