Good day
I want to create a report on data that is missing in my table. For example if I have data:
Country City Quarter Amount
France Paris 99Q1 10
France Paris 99Q2 15
France Paris 99Q4 20
France Bordeaux 99Q3 50
France Bordeaux 99Q1 50
Italy Rome 99Q2 10
Italy Milan 99Q4 8
Where Quarter is a YYQ. format. I want to create a report summarising the countries that have not provided any amount figures for 99Q3 or 99Q4. Thus my output should be
Report
Country Missing quarter Cities missing Quarter
France 99Q3 Paris
France 99Q4 Bordeaux
Italy 99Q3 Rome, Milan
Italy 99Q4 Rome
Any ideas on how to achieve this? The code I wrote extracts exactly the opposite of what I want where I took the Quarter to a SAS date, How can I change it to get the right answer?
Data Amount; set Sasuser.Amounts; where Quarter='SAS Date for 99Q3' or Quarter='SAS date for 99Q4'; run; proc print data=Work.Amount; run; Proc report Data=Work.Amount nofs headline headskip; Columns Country Date City; define Country / Group ; define Quarter / Group ; Define City/ across; run;
Datastep, with a retain for cities and a lag. It would of course be far simpler if you put proper dates in, even if only with a default day and year, as Jan means nothing in context.
data want; set have;
by country; retain miss_cities; length miss_cities miss_month $2000; if first.country then miss_cities=""; else do; if intnx('month',yourdate,-1) ne lag(yourdate) then do; miss_cities=catx(",",miss_cities,city); miss_month=month(yourdate); end;
if last.country then output; run;
Note the above works if you have a proper date variable, I am not faffing about converting a random text string to a date, you can do that. And in future please put test data in a datastep, using the code window, which is the {i} above the post area.
Is this specific to March or do you really want to find all missing for each month?
My date format is YYQ4. I just tried to simplify my question. I want the missing for two dates.
If the solution from @RW9 doesn't work, I think you've simplified your question too much and need to add some better example data.
For starters though are your months actually character or are they SAS dates?
@HelpPlease wrote:
My date format is YYQ4. I just tried to simplify my question. I want the missing for two dates.
They were Characters but I converted them to the SAS format YYQ4.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.