BookmarkSubscribeRSS Feed
FerhatD
Calcite | Level 5

Hello experts,

 

I have a question about reading from table. For example; I have a lookup table like:

 

COUNTRY_CODE              COUNTRY_SCORE

USA                                       100

TUR                                       140

ENG                                       120

 

I would like to create a parametric code which can find the COUNTRY_SCORE by given COUNTRY_CODE. It means when I run the code and if “USA” is given for COUNTRY_CODE I want to find the “100” for C COUNTRY_SCORE for future calculation.

 

I tried something as given below but the code couldn’t resolve the macros so couldn’t find the scores.

 

Thanks in advance

 

 

 

%macro mAssign(VR, VL);

 

data _null_;

call symput ("&VR.","&&VL.");

run;

 

%mend mAssign;

 

data _null_;

set lookup_dim;

call execute('%mAssign('||LOOKUP_CODE||','||LOOKUP_NUM_VALUE||')');

run;

 

data country;

   input COUNTRY_CODE $;

   datalines;

USA

TUR

ENG

;run;

 

data scored_country;

set country;

if COUNTRY_CODE = "&LOOKUP_CODE" then country_score = &LOOKUP_NUM_VALUE; else country_score = 0;

run;

 

 

2 REPLIES 2
lrudolphi
Obsidian | Level 7

Why not use the small lookup table - I'm calling it country_lookup - in a hash join to the larger table with countries? Seems more straightforward than complex macro code. If you haven't used a hash join before, it's a simple in data step way to combine tables and often more computationally efficient than proc sql or a sort-merge.

data has_country_score;
	length country_score 8.;
	if _n_ eq 1 then do;
		declare hash ctry_hash(dataset:'country_lookup');
		ctry_hash.definekey('country_code');
		ctry_hash.definedata('country_score');
		ctry_hash.definedone();
		call missing(country_score);
	end;

	set big_table_with_countries;

	rcLOOKUP = ctry_hash.find(); /*Will be 0 if there is a match*/
run;
s_lassen
Meteorite | Level 14

There are several ways to skin this cat. One is to create an informat, e.g.:

data have;
  input
COUNTRY_CODE  $ COUNTRY_SCORE;
cards;
USA 100
TUR 140
ENG 120
;run;

filename tempsas temp;
data _null_;
  set have end=done;
  file tempsas;
  if _N_=1 then
    put 'invalue score';
  put country_code $quote. '=' country_score;
  if done then
    put 'other=.;';
run;

proc format;
  %include tempsas /source2;
run;

data _null_;
  a='USA';
  b=input(a,score.);
  put _all_;
run;

Another is to create an index and use that for lookup:

proc sql;
  create index country_code on have(country_code);
quit;

data _null_;
  input a $;
  set have(rename=(country_code=a)) key=a/unique;
  if _iorc_ then do;
    _error_=0;
    country_score=.;
    end;
  put _all_;
cards;
TUR
USA
FFF
;run;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 2 replies
  • 778 views
  • 2 likes
  • 3 in conversation