dataset : populating variables

[Arora_S]

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

[Reeza]

Look at the PRELOADFMT option in PROC TABULATE

[Arora_S]

does it work with proc report

[Reeza · Accepted solution]

Yes, it does, see examples in the paper below.

http://support.sas.com/resources/papers/proceedings11/239-2011.pdf

[Arora_S]

works..thanks much

[ChrisNZ]

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

[Arora_S]

Cool.

[Arora_S]

This solutions works as well. Accepted as solution.

thanks much for your innovative approach.