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;

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
  • 2 replies
  • 668 views
  • 2 likes
  • 3 in conversation