BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Arora_S
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION
7 REPLIES 7
Reeza
Super User

Look at the PRELOADFMT option in PROC TABULATE

Arora_S
Obsidian | Level 7

does it work with proc report

 

Arora_S
Obsidian | Level 7
works..thanks much
ChrisNZ
Tourmaline | Level 20

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

Cool.

Arora_S
Obsidian | Level 7

This solutions works as well. Accepted as solution.

thanks much for your innovative approach.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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