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.
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;
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.
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.
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;
This really helped and put me on the right track. Thank you so much!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.