have an interesting sas question, I have 2 sas data sets as below:
this is the master dataset
Tshirt | city | count |
yellow | dubai | 1 |
brown | newyork | 2 |
blue | dallas | 3 |
and a look up datset for all possible values of city
city |
dubai |
newyork |
dallas |
los angeles |
chicago |
philadelphia |
I want to update the master data with values from the look up - for any missing values of city and related count if it is not there then in master then it should have the city and value of 0 . so final result should look like
as below:
City | ||||||
Tshirt | dubai | newyork | dallas | los angeles | chicago | philadelphia |
yellow | 1 | 0 | 0 | 0 | 0 | 0 |
brown | 0 | 2 | 0 | 0 | 0 | 0 |
blue | 0 | 0 | 3 | 0 | 0 | 0 |
thanks for help with this
Yes, it does, see examples in the paper below.
http://support.sas.com/resources/papers/proceedings11/239-2011.pdf
Look at the PRELOADFMT option in PROC TABULATE
does it work with proc report
Yes, it does, see examples in the paper below.
http://support.sas.com/resources/papers/proceedings11/239-2011.pdf
Like this?
data MASTER;
input COLOUR $6. CITY : $12. COUNT : 1.;
cards;
yellow dubai 1
brown newyork 2
blue dallas 3
brown dallas 3
run;
data CITIES;
input CITY :& $12.;
cards;
dubai
newyork
dallas
los angeles
chicago
philadelphia
run;
%* Merge tables;
proc sql;
create table MERGE as
select unique cartesian.COLOUR
, cartesian.CITY
, coalesce(master.COUNT,0) as COUNT
from (select unique COLOUR, CITIES.CITY
from MASTER, CITIES) as cartesian
left join
MASTER as master
on master.CITY =cartesian.CITY
and master.COLOUR=cartesian.COLOUR
order by COLOUR ;
quit;
%* Rotate table;
proc transpose data=MERGE
out =WANT(drop=_NAME_);
id CITY;
by COLOUR;
var COUNT;
run;
proc print data=WANT noobs;
run;
COLOUR | chicago | dallas | dubai | los angeles | newyork | philadelphia |
---|---|---|---|---|---|---|
blue | 0 | 3 | 0 | 0 | 0 | 0 |
brown | 0 | 3 | 0 | 0 | 2 | 0 |
yellow | 0 | 0 | 1 | 0 | 0 | 0 |
Cool.
This solutions works as well. Accepted as solution.
thanks much for your innovative approach.
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.