BookmarkSubscribeRSS Feed
HelpPlease
Calcite | Level 5

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;

 

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

HelpPlease
Calcite | Level 5
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
Reeza
Super User

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

 

 

HelpPlease
Calcite | Level 5

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

Reeza
Super User

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.


 

HelpPlease
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 800 views
  • 0 likes
  • 3 in conversation