DATA Step, Macro, Functions and more

Creating a report on data that is not there

Reply
Occasional Contributor
Posts: 12

Creating a report on data that is not there

[ Edited ]

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;

 

Super User
Super User
Posts: 9,386

Re: Creating a report on data that is not there

Posted in reply to HelpPlease

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.

Occasional Contributor
Posts: 12

Re: Creating a report on data that is not there

I think I simplified my Question too much; I edited it now; can you maybe check if you know how to help me now. Thank you in advance
Super User
Posts: 23,224

Re: Creating a report on data that is not there

Posted in reply to HelpPlease

Is this specific to March or do you really want to find all missing for each month?

 

 

Occasional Contributor
Posts: 12

Re: Creating a report on data that is not there

My date format is YYQ4. I just tried to simplify my question. I want the missing for two dates.

Super User
Posts: 23,224

Re: Creating a report on data that is not there

Posted in reply to HelpPlease

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.


 

Occasional Contributor
Posts: 12

Re: Creating a report on data that is not there

They were Characters but I converted them to the SAS format YYQ4.  

Ask a Question
Discussion stats
  • 6 replies
  • 185 views
  • 0 likes
  • 3 in conversation