topic Re: dataset : populating variables in SAS Programming

dataset : populating variables

Arora_S — Wed, 14 Dec 2016 20:07:10 GMT

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

Re: dataset : populating variables

Reeza — Wed, 14 Dec 2016 21:04:14 GMT

Look at the PRELOADFMT option in PROC TABULATE

Re: dataset : populating variables

Arora_S — Wed, 14 Dec 2016 21:06:59 GMT

does it work with proc report

Re: dataset : populating variables

Reeza — Wed, 14 Dec 2016 21:14:09 GMT

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

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

Re: dataset : populating variables

Arora_S — Wed, 14 Dec 2016 21:22:03 GMT

works..thanks much

Re: dataset : populating variables

ChrisNZ — Wed, 14 Dec 2016 21:24:40 GMT

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	dallas	dubai	newyork
blue	3	0	0
brown	3	0	2
yellow	0	1	0

Re: dataset : populating variables

Arora_S — Fri, 16 Dec 2016 15:46:16 GMT

Cool.

Re: dataset : populating variables

Arora_S — Fri, 23 Dec 2016 15:46:52 GMT

This solutions works as well. Accepted as solution.

thanks much for your innovative approach.