BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buffalol
Fluorite | Level 6

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. 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Reeza
Super User

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. 

 

 

Reeza
Super User

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;

 

 

 

buffalol
Fluorite | Level 6

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

Ksharp
Super User
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;

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
  • 5 replies
  • 802 views
  • 2 likes
  • 4 in conversation