topic Re: Creating a report of data that is NOT in the dataset. in SAS Programming

Creating a report of data that is NOT in the dataset.

buffalol — Sun, 04 Jun 2017 20:55:47 GMT

Hello,

I am new to SAS, and am struggling with a particular question and would just like to be pointed in the right direction.

I have a dataset of multiple product listings and their costs per year. So for example:

Product 1 1999 $566

Product 1 2000 $500

Product 2 1998 $700

Product 2 2000 $400

Product 3 1998 $300

I want to be able to print a list of all the products that don't have a price for the year 1998 and 1999. So since the data doesn't exist in the dataset, it needs to be created without manually adding in the data. The result needs to look something like this:

1998 Product 1

1999 Product 2, Product 3

Hopefully this makes sense. I would appreciate any and all feedback! I'm not even sure how to begin with listing data or referencing rows that essentially don't exist. Keep in mind, this is a small example and that the dataset I'm working on has 100s of rows.

Thanks.

Re: Creating a report of data that is NOT in the dataset.

PaigeMiller — Sun, 04 Jun 2017 21:05:16 GMT

You can create a data set with all possible combinations of product and year.

Then, you can use either PROC SQL or a data step merge to identify which rows of the all possible combinations data set do not match a row in your actual data set.

Re: Creating a report of data that is NOT in the dataset.

Reeza — Sun, 04 Jun 2017 21:08:44 GMT

What years are you interested in? What products?

Either way, search PRELOADFMT that shows a way to get all possible combinations and building the table you want. This is a pretty common question on here.

Re: Creating a report of data that is NOT in the dataset.

Reeza — Sun, 04 Jun 2017 21:16:04 GMT

If you know that each year and product is recorded at least once in the data set somewhere you can use the SPARSE option in PROC FREQ.

Here's an example with PROC FREQ.

proc freq data=sashelp.class noprint;
table sex*age /out = want (where=(count=0)) sparse nopercent;
run;

proc print data=want;
run;

Re: Creating a report of data that is NOT in the dataset.

Ksharp — Mon, 05 Jun 2017 14:31:27 GMT

data have;
input p $ y d : dollar.;
cards;
Product1 1999 $566
Product1 2000 $500
Product2 1998 $700
Product2 2000 $400
Product3 1998 $300
;
run;
data temp;
 y=1998;output;
 y=1999;output;
run;
proc sql;
create table want as
 select distinct a.p,a.y
  from (
select * from
(select distinct p from have),
(select y from temp)
) as a left join have as b
on a.p=b.p and a.y=b.y 
where d is missing;
quit;

Re: Creating a report of data that is NOT in the dataset.

buffalol — Mon, 12 Jun 2017 07:58:48 GMT

This really helped and put me on the right track. Thank you so much!