Solved
Contributor
Posts: 35

# 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

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

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

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
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

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.