topic Re: Inserting blank row with Date in SAS Programming

Inserting blank row with Date

mmaleta851 — Wed, 09 Feb 2022 19:46:44 GMT

Suppose I have the following:

data table;
input Id Date:YYQ8. Trips Sales;
format Date YYQ8.;
datalines; 
1 2021Q1 10 100
1 2021Q3 2 200
1 2021Q4 4 400
2 2021Q1 16 300
2 2021Q2 8 600
2 2021Q2 19 100;
run;

Id	Date	Trips	Sales
1	2021Q1	10	100
1	2021Q3	2	200
1	2021Q4	4	400
2	2021Q1	16	300
2	2021Q2	8	600
2	2021Q4	19	100

For each id, I want to insert a blank for each date there wasn't a sale as such:

Id	Date	Trips	Sales
1	2021Q1	10	100
2	2021Q2	.	.
1	2021Q3	2	200
1	2021Q4	4	400
2	2021Q1	16	300
2	2021Q2	8	600
2	2021Q3	.	.
2	2021Q4	19	100

I want to do this so I can do quarter-over-quarter analysis. How would I accomplish this?

Re: Inserting blank row with Date

PeterClemmensen — Wed, 09 Feb 2022 20:12:56 GMT

If you have SAS/ETS license, then Proc Timeseries is the right tool.

data table;
input Id Date:YYQ8. Trips Sales;
format Date YYQ8.;
datalines; 
1 2021Q1 10 100 
1 2021Q3 2  200 
1 2021Q4 4  400 
2 2021Q1 16 300 
2 2021Q2 8  600 
2 2021Q4 19 100 
;
run;

proc timeseries data = table out = want;
   by ID;
   id Date interval   = quarter
           accumulate = none
           setmiss    = missing
           format     = YYQ8.;
   var Trips Sales;
run;

Result:

Id  Date    Trips  Sales 
1   2021Q1  10     100 
1   2021Q2  .      . 
1   2021Q3  2      200 
1   2021Q4  4      400 
2   2021Q1  16     300 
2   2021Q2  8      600 
2   2021Q3  .      . 
2   2021Q4  19     100

Re: Inserting blank row with Date

mmaleta851 — Wed, 09 Feb 2022 21:24:24 GMT

That works. Thank you!

Re: Inserting blank row with Date

mmaleta851 — Wed, 09 Feb 2022 21:47:07 GMT

Additionally what happens if I have this

ID	Date	Trips	Sales
1	2021Q2	5	100
1	2021Q3	10	200

And I want to add a blank row before or after to get this?

ID	Date	Trips	Sales
1	2021Q1	.	.
1	2021Q2	5	100
1	2021Q3	10	200
1	2021Q4	.	.

Re: Inserting blank row with Date

PeterClemmensen — Thu, 10 Feb 2022 07:23:58 GMT

If you have specific start / end dates, you can place them in the ID Statement like this

proc timeseries data = table out = want;
   by ID;
   id Date interval   = quarter
           accumulate = none
           setmiss    = missing
		   start      = '01jan2021'd
		   end        = '31dec2021'd
           format     = YYQ8.;
   var Trips Sales;
run;

Re: Inserting blank row with Date

Ksharp — Thu, 10 Feb 2022 08:40:20 GMT

data table;
input Id Date:YYQ8. Trips Sales;
format Date YYQ8.;
datalines; 
1 2021Q1 10 100 
1 2021Q3 2  200 
1 2021Q4 4  400 
2 2021Q1 16 300 
2 2021Q2 8  600 
2 2021Q4 19 100 
;
run;
proc sql;
create table year as
select id,year(min(date)) as min,year(max(date)) as max from table group by id;
quit;
data yyq;
 set year;
 do year=min to max;
  do qtr=1 to 4;
   date=yyq(year,qtr);output;
  end;
 end;
format date yyq.;
keep id date;
run;
proc sql;
create table want as
select a.*, Trips ,Sales
 from yyq as a left join table as b on a.id=b.id and a.date=b.date;
quit;

Re: Inserting blank row with Date

s_lassen — Thu, 10 Feb 2022 14:14:42 GMT

And if you do not have SAS/ETS, something like this may do the trick:

proc sql;
  create table quarters as select table.id,quarters.date
  from (select distinct date from table) quarters,
       (select distinct id from table) table
  ;
  create table want as select quarters.id,quarters.date,table.trips,table.sales
  from quarters left join table on quarters.id=table.id and quarters.date=table.date
  ;

That is, first select all IDs and QUARTERs from the table, and then left join to get the wanted result. But this will only work if your DATE values are actually the same for each quarter (all the first day in the quarter).

Re: Inserting blank row with Date

mmaleta851 — Thu, 10 Feb 2022 20:07:41 GMT

Thank you!