DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

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

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. 

 

 

 

 


Accepted Solutions
Solution
‎06-12-2017 03:58 AM
Super User
Posts: 17,837

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

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;

 

 

 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,615

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

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.

Super User
Posts: 17,837

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

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. 

 

 

Solution
‎06-12-2017 03:58 AM
Super User
Posts: 17,837

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

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;

 

 

 

Occasional Contributor
Posts: 9

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

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

Super User
Posts: 9,681

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

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 176 views
  • 2 likes
  • 4 in conversation