topic Reading the values parametrically from the columns in SAS Programming

Reading the values parametrically from the columns

FerhatD — Fri, 22 Jun 2018 11:25:45 GMT

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;

Re: Reading the values parametrically from the columns

lrudolphi — Fri, 22 Jun 2018 12:19:46 GMT

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;

Re: Reading the values parametrically from the columns

s_lassen — Fri, 22 Jun 2018 13:03:50 GMT

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;