DATA Step, Macro, Functions and more

dataset : populating variables

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

dataset : populating variables

[ Edited ]

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

 


Accepted Solutions
Solution
‎12-14-2016 04:21 PM
Super User
Posts: 19,832

Re: dataset : populating variables


All Replies
Super User
Posts: 19,832

Re: dataset : populating variables

Look at the PRELOADFMT option in PROC TABULATE

Contributor
Posts: 35

Re: dataset : populating variables

does it work with proc report

 

Solution
‎12-14-2016 04:21 PM
Super User
Posts: 19,832

Re: dataset : populating variables

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

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

Contributor
Posts: 35

Re: dataset : populating variables

works..thanks much
PROC Star
Posts: 1,760

Re: dataset : populating variables

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

 

Contributor
Posts: 35

Re: dataset : populating variables

Cool.

Contributor
Posts: 35

Re: dataset : populating variables

This solutions works as well. Accepted as solution.

thanks much for your innovative approach.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 273 views
  • 3 likes
  • 3 in conversation