How to generate dataset with macro function and looping?

[SASnewbie0]

Hi community! I am not familiar with SAS macro function.

I got a dataset like the one below:

I would like to add row (in red).

I have an idea creating records with macro function and looping.

I have created an array for month and table with shop that need generate records.

Array:

_N_      month

1          2023-01

2         2023-02

3         2023-03

Table:

store      cnt

B             2

C             1

Any idea on getting a dataset  like the one below?

[s_lassen]

One possibility may be to create a table containing all the store IDs and months that you want, and then merge with the original:

data all;
  set have(keep=store);
  by  store;
  if first.store;
  do month='2023-01','2023-02','2023-03';
    output;
    end;
run;

data want;
  merge have(in=ok) all;
  by store month;
  if not ok then
    sales=-999;
run;
  

I do not think there is any reason to use macros for this.

[mkeintz]

I agree with @s_lassen 's code showing there is no need for a macro.

One minor suggestion that could greatly improve efficiency if the dataset is large.  Generate ALL as a dataset VIEW rather than as a dataset FILE.  This will allow the two passthroughs of HAVE to be synchronized rather than one at a time.

{added comment).  As a result there will be no need to write the intermediate dataset ALL to disk, just to immediately reread it.

data ALL / view=ALL;
  ...
data WANT;
  ...

[Patrick · Accepted solution]

Below approach is performance wise not optimal but it's easy to code and is dynamic.

data have;
  input store $ _dt $ sales;
  format date yymmd7.;
  date=input(catx('-',_dt,'01'),yymmdd10.);
  drop _dt;
datalines;
A 2023-01 100
A 2023-02 50
A 2023-03 120
B 2023-02 110
B 2023-03 90
C 2023-03 80
;
run;

proc sql;
/*  create table want as*/
  select 
    t1.store
    ,t2.date
    ,coalesce(t3.sales,-999) as sales

  from (select distinct store from have) t1 

  full join (select distinct date from have) t2
  on 1

  left join have t3
  on t3.store=t1.store and t3.date=t2.date
  ;
quit;

If you just need to ensure that you've got always the same values for categorical variables when it comes to reporting then eventually do not modify your source table but look into the classdata option available with certain procedures like Proc Means and Proc Tabulate.

And for reporting if you want to print missing values as -999 then look into SAS option missing=...