topic Re: Creating a report on data that is not there in SAS Programming

Creating a report on data that is not there

HelpPlease — Fri, 02 Feb 2018 12:32:48 GMT

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;

Re: Creating a report on data that is not there

RW9 — Thu, 01 Feb 2018 15:03:12 GMT

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.

Re: Creating a report on data that is not there

Reeza — Thu, 01 Feb 2018 15:52:15 GMT

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

Re: Creating a report on data that is not there

HelpPlease — Thu, 01 Feb 2018 15:54:46 GMT

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

Re: Creating a report on data that is not there

Reeza — Thu, 01 Feb 2018 16:03:23 GMT

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.

Re: Creating a report on data that is not there

HelpPlease — Thu, 01 Feb 2018 16:15:24 GMT

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

Re: Creating a report on data that is not there

HelpPlease — Fri, 02 Feb 2018 12:28:24 GMT

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