Inserting blank row with Date

[mmaleta851]

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?

[PeterClemmensen · Accepted solution]

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 

[mmaleta851]

That works. Thank you!

[mmaleta851]

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	.	.

[PeterClemmensen]

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;

[mmaleta851]

Thank you!

[Ksharp]

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;

[s_lassen]

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).