What do when referencing a macro variable which value is 0?

[ANKH1]

Hi! In SAS, I have a code

proc sql;
select count (distinct ID) into: sam1 : sam3 from ds where group=1 group by visit;
quit;

But for sam1 there are 0 observations. SAS results only shows sample sizes for sam2 and sam3. I reference these macro variables somewhere else in a data step like this:

data temp;
length visit1 visit2 visit3;
set temp;
if order (1,4,7) then do visit1="N=%left(&sam1)";visit2="N=%left(&sam2)";visit3="N=%left(&sam3)";end;
run;

I then get a warning that apparent symbolic reference sam3 not resolved. But it is shifting the sample sizes. How can we solve this, when a sample size is 0?

[ballardw]

I have no idea what "shifting sample sizes" means.

The way your code currently reads the count of the first distinct Id value present goes into the first macro variable, the second value present into the second and if present the third value goes into the the third macro variable. Distinct also tends to order the values so it is very likely the values are seldom going to match your desired order especially with the Group By condition added in.

If you need specific results in specific macro variables then you need to set conditions for each macro variable separately. And possibly before that assign default values such as 0 for the ones that have no actual values in the source data.

I won't say this is impossible to do with sql in a single pass but may result in complex and somewhat fragile code. 

I would strongly suggest providing an example of the DS and TEMP data sets in the form of working data sets and describe the generic logic of what you want. It appears that your macro variables should have some dependency on the value of the variable ORDER but we can't even tell if it is in DS data set.