I'm using 2 arrays of elements to compare values across 175 different data elements for 2 matching records (using a hash to bring in the comparison records); a data entry quality control check. Currently when there's a discrepancy, I assign the i (increment) value to the variable question to help me identify where the discripancy is. What I'd like to do is assign the variable name to the variable question. Does anyone know how to do this?
I thought I could put all the variables into a macro variable
%let vars=BIRTH Before2009 DAY DAYDOB LANGUAGE MONTH MONTHDOB...;
then use %scan to assign the comparison variable to the variable question (i.e. question=%scan(&vars,i);), but I get an error message that I'm using a character value, i, where a numeric value should be. I'm guessing that I can't use a data step variable in a macro function.
The code below works, but gives me the i value rather than the variable name.
Thanks,
Ryan
data matches; /* Matching death dataset to CFRT data using a hash */
keep RecordNo Lang Question QuesProVal ScantVal;
if _N_ = 1 then do;
declare hash h(dataset:'QuesPro', multidata: 'yes');
h.defineKey('QuesPro_serialNo');
h.defineData('QuesPro_BIRTH', 'QuesPro_Before2009', 'QuesPro_DAY', 'QuesPro_DAYDOB', 'QuesPro_LANGUAGE', 'QuesPro_MONTH', 'QuesPro_MONTHDOB', 'QuesPro_Q1', 'QuesPro_Q2', 'QuesPro_Q3', 'QuesPro_Q4', 'QuesPro_Q5', 'QuesPro_Q6', 'QuesPro_Q7', 'QuesPro_Q8', 'QuesPro_Q9', 'QuesPro_Q10', 'QuesPro_Q13', 'QuesPro_Q15', 'QuesPro_Q16', 'QuesPro_Q19', 'QuesPro_Q20', 'QuesPro_Q21', 'QuesPro_Q23', 'QuesPro_Q24', 'QuesPro_Q28', 'QuesPro_Q30', 'QuesPro_Q31', 'QuesPro_Q33', 'QuesPro_Q34', 'QuesPro_Q35', 'QuesPro_Q36', 'QuesPro_Q37', 'QuesPro_Q40', 'QuesPro_Q41', 'QuesPro_Q42', 'QuesPro_Q43', 'QuesPro_Q44', 'QuesPro_Q45', 'QuesPro_Q48', 'QuesPro_Q49', 'QuesPro_Q50', 'QuesPro_Q53', 'QuesPro_Q54', 'QuesPro_Q57', 'QuesPro_Q11A', 'QuesPro_Q11B', 'QuesPro_Q11C', 'QuesPro_Q14A', 'QuesPro_Q14B', 'QuesPro_Q14C', 'QuesPro_Q14D', 'QuesPro_Q14E', 'QuesPro_Q14F', 'QuesPro_Q14G', 'QuesPro_Q14H', 'QuesPro_Q14I', 'QuesPro_Q14J', 'QuesPro_Q17A', 'QuesPro_Q17B', 'QuesPro_Q17C', 'QuesPro_Q17D', 'QuesPro_Q17E', 'QuesPro_Q17F', 'QuesPro_Q17G', 'QuesPro_Q17H', 'QuesPro_Q17I', 'QuesPro_Q18A', 'QuesPro_Q18B', 'QuesPro_Q18C', 'QuesPro_Q18D', 'QuesPro_Q18E', 'QuesPro_Q18F', 'QuesPro_Q18G', 'QuesPro_Q18H', 'QuesPro_Q18I', 'QuesPro_Q18J', 'QuesPro_Q18K', 'QuesPro_Q18L', 'QuesPro_Q18M', 'QuesPro_Q18N', 'QuesPro_Q22A', 'QuesPro_Q22B', 'QuesPro_Q22C', 'QuesPro_Q25A', 'QuesPro_Q25B', 'QuesPro_Q26A', 'QuesPro_Q26B', 'QuesPro_Q26C', 'QuesPro_Q26D', 'QuesPro_Q26E', 'QuesPro_Q26F', 'QuesPro_Q26G', 'QuesPro_Q26H', 'QuesPro_Q26I', 'QuesPro_Q26J', 'QuesPro_Q26K', 'QuesPro_Q26L', 'QuesPro_Q26M', 'QuesPro_Q26N', 'QuesPro_Q26O', 'QuesPro_Q27A', 'QuesPro_Q27B', 'QuesPro_Q27C', 'QuesPro_Q27D', 'QuesPro_Q27E', 'QuesPro_Q27F', 'QuesPro_Q27G', 'QuesPro_Q27H', 'QuesPro_Q27I', 'QuesPro_Q27J', 'QuesPro_Q27K', 'QuesPro_Q27L', 'QuesPro_Q27M', 'QuesPro_Q27N', 'QuesPro_Q27O', 'QuesPro_Q27P', 'QuesPro_Q27Q', 'QuesPro_Q27R', 'QuesPro_Q29A', 'QuesPro_Q29B', 'QuesPro_Q29C', 'QuesPro_Q29D', 'QuesPro_Q32A', 'QuesPro_Q32B', 'QuesPro_Q32C', 'QuesPro_Q32D', 'QuesPro_Q32E', 'QuesPro_Q32F', 'QuesPro_Q38A', 'QuesPro_Q38B', 'QuesPro_Q38C', 'QuesPro_Q39A', 'QuesPro_Q39B', 'QuesPro_Q39C', 'QuesPro_Q46A', 'QuesPro_Q46B', 'QuesPro_Q47A', 'QuesPro_Q47B', 'QuesPro_Q51A', 'QuesPro_Q51B', 'QuesPro_Q51C', 'QuesPro_Q51D', 'QuesPro_Q51E', 'QuesPro_Q52A', 'QuesPro_Q52B', 'QuesPro_Q52C', 'QuesPro_Q52D', 'QuesPro_Q52E', 'QuesPro_Q55A', 'QuesPro_Q55B', 'QuesPro_Q55C', 'QuesPro_Q55D', 'QuesPro_Q55E', 'QuesPro_Q55F', 'QuesPro_Q55G', 'QuesPro_Q55H', 'QuesPro_Q55I', 'QuesPro_Q56A1', 'QuesPro_Q56A2', 'QuesPro_Q56A3', 'QuesPro_Q56A4', 'QuesPro_Q56A5', 'QuesPro_Q56A6', 'QuesPro_Q56B1', 'QuesPro_Q56B2', 'QuesPro_Q56B3', 'QuesPro_Q56B4', 'QuesPro_Q56B5', 'QuesPro_Q56B6', 'QuesPro_YEAR', 'QuesPro_YEARDOB', 'QuesPro_q12a', 'QuesPro_q12b', 'QuesPro_serialNo');
h.defineDone();
end;
set ScanTrons;
declare hiter iter("h");
rc = iter.first();
do while (rc=0);
array Ques[175] QuesPro_BIRTH QuesPro_Before2009 QuesPro_DAY QuesPro_DAYDOB QuesPro_LANGUAGE QuesPro_MONTH QuesPro_MONTHDOB QuesPro_Q1 QuesPro_Q2 QuesPro_Q3 QuesPro_Q4 QuesPro_Q5 QuesPro_Q6 QuesPro_Q7 QuesPro_Q8 QuesPro_Q9 QuesPro_Q10 QuesPro_Q13 QuesPro_Q15 QuesPro_Q16 QuesPro_Q19 QuesPro_Q20 QuesPro_Q21 QuesPro_Q23 QuesPro_Q24 QuesPro_Q28 QuesPro_Q30 QuesPro_Q31 QuesPro_Q33 QuesPro_Q34 QuesPro_Q35 QuesPro_Q36 QuesPro_Q37 QuesPro_Q40 QuesPro_Q41 QuesPro_Q42 QuesPro_Q43 QuesPro_Q44 QuesPro_Q45 QuesPro_Q48 QuesPro_Q49 QuesPro_Q50 QuesPro_Q53 QuesPro_Q54 QuesPro_Q57 QuesPro_Q11A QuesPro_Q11B QuesPro_Q11C QuesPro_Q14A QuesPro_Q14B QuesPro_Q14C QuesPro_Q14D QuesPro_Q14E QuesPro_Q14F QuesPro_Q14G QuesPro_Q14H QuesPro_Q14I QuesPro_Q14J QuesPro_Q17A QuesPro_Q17B QuesPro_Q17C QuesPro_Q17D QuesPro_Q17E QuesPro_Q17F QuesPro_Q17G QuesPro_Q17H QuesPro_Q17I QuesPro_Q18A QuesPro_Q18B QuesPro_Q18C QuesPro_Q18D QuesPro_Q18E QuesPro_Q18F QuesPro_Q18G QuesPro_Q18H QuesPro_Q18I QuesPro_Q18J QuesPro_Q18K QuesPro_Q18L QuesPro_Q18M QuesPro_Q18N QuesPro_Q22A QuesPro_Q22B QuesPro_Q22C QuesPro_Q25A QuesPro_Q25B QuesPro_Q26A QuesPro_Q26B QuesPro_Q26C QuesPro_Q26D QuesPro_Q26E QuesPro_Q26F QuesPro_Q26G QuesPro_Q26H QuesPro_Q26I QuesPro_Q26J QuesPro_Q26K QuesPro_Q26L QuesPro_Q26M QuesPro_Q26N QuesPro_Q26O QuesPro_Q27A QuesPro_Q27B QuesPro_Q27C QuesPro_Q27D QuesPro_Q27E QuesPro_Q27F QuesPro_Q27G QuesPro_Q27H QuesPro_Q27I QuesPro_Q27J QuesPro_Q27K QuesPro_Q27L QuesPro_Q27M QuesPro_Q27N QuesPro_Q27O QuesPro_Q27P QuesPro_Q27Q QuesPro_Q27R QuesPro_Q29A QuesPro_Q29B QuesPro_Q29C QuesPro_Q29D QuesPro_Q32A QuesPro_Q32B QuesPro_Q32C QuesPro_Q32D QuesPro_Q32E QuesPro_Q32F QuesPro_Q38A QuesPro_Q38B QuesPro_Q38C QuesPro_Q39A QuesPro_Q39B QuesPro_Q39C QuesPro_Q46A QuesPro_Q46B QuesPro_Q47A QuesPro_Q47B QuesPro_Q51A QuesPro_Q51B QuesPro_Q51C QuesPro_Q51D QuesPro_Q51E QuesPro_Q52A QuesPro_Q52B QuesPro_Q52C QuesPro_Q52D QuesPro_Q52E QuesPro_Q55A QuesPro_Q55B QuesPro_Q55C QuesPro_Q55D QuesPro_Q55E QuesPro_Q55F QuesPro_Q55G QuesPro_Q55H QuesPro_Q55I QuesPro_Q56A1 QuesPro_Q56A2 QuesPro_Q56A3 QuesPro_Q56A4 QuesPro_Q56A5 QuesPro_Q56A6 QuesPro_Q56B1 QuesPro_Q56B2 QuesPro_Q56B3 QuesPro_Q56B4 QuesPro_Q56B5 QuesPro_Q56B6 QuesPro_YEAR QuesPro_YEARDOB QuesPro_q12a QuesPro_q12b QuesPro_serialNo ;
array Scant[175] BIRTH Before2009 DAY DAYDOB LANGUAGE MONTH MONTHDOB Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q13 Q15 Q16 Q19 Q20 Q21 Q23 Q24 Q28 Q30 Q31 Q33 Q34 Q35 Q36 Q37 Q40 Q41 Q42 Q43 Q44 Q45 Q48 Q49 Q50 Q53 Q54 Q57 Q11A Q11B Q11C Q14A Q14B Q14C Q14D Q14E Q14F Q14G Q14H Q14I Q14J Q17A Q17B Q17C Q17D Q17E Q17F Q17G Q17H Q17I Q18A Q18B Q18C Q18D Q18E Q18F Q18G Q18H Q18I Q18J Q18K Q18L Q18M Q18N Q22A Q22B Q22C Q25A Q25B Q26A Q26B Q26C Q26D Q26E Q26F Q26G Q26H Q26I Q26J Q26K Q26L Q26M Q26N Q26O Q27A Q27B Q27C Q27D Q27E Q27F Q27G Q27H Q27I Q27J Q27K Q27L Q27M Q27N Q27O Q27P Q27Q Q27R Q29A Q29B Q29C Q29D Q32A Q32B Q32C Q32D Q32E Q32F Q38A Q38B Q38C Q39A Q39B Q39C Q46A Q46B Q47A Q47B Q51A Q51B Q51C Q51D Q51E Q52A Q52B Q52C Q52D Q52E Q55A Q55B Q55C Q55D Q55E Q55F Q55G Q55H Q55I Q56A1 Q56A2 Q56A3 Q56A4 Q56A5 Q56A6 Q56B1 Q56B2 Q56B3 Q56B4 Q56B5 Q56B6 YEAR YEARDOB q12a q12b serialNo ;
do i=1 to 175;
if serialNo eq QuesPro_serialNo and language eq QuesPro_language then do;
RecordNo=serialNo;
Lang=language;
if ques{i} ne Scant{i} then do;
Question=i; QuesProVal=Ques{i}; ScantVal=Scant{i}; output;
end;
end;
end;
rc = iter.next();
end;
run;
Can't you just use the vname() function? E.g. in your line:
Question=i; QuesProVal=Ques{i}; ScantVal=Scant{i}; output;
include something like ScantName=vname(Scant(i)
Can't you just use the vname() function? E.g. in your line:
Question=i; QuesProVal=Ques{i}; ScantVal=Scant{i}; output;
include something like ScantName=vname(Scant(i)
Use the VNAME function as Art suggested.
As to your detailed question about using scan, there is no problem using the SCAN function in a datastep.
varname = scan("&vars", i );
But there is no point in using the macro %SCAN function as that will only execute once while the data step is being compiled.
OP has the right idea to use a hash object to efficiently execute this kind of join. For each observation on one side (let's say dataset S), you loop over possibly multiple observations from the other side (dataset Q). The efficiency comes from looping over only those multiple observations that have matching id, instead of looping over all the observations on the dataset Q. At least I think the OP's intention was this. Unfortunately, OP's code shows that it is looping over all the Q observations given each S observation and is not efficient at all. I recommend careful reading of Ray and Secosky (2008).
/* test data */
data s;
input id lang $ v1 v2;
cards;
1 a 1 2
2 b 3 7
3 x 8 9
;
run;
data q;
input id q_lang $ q_v1 q_v2;
cards;
1 a 1 2
2 b 3 4
2 b 5 6
3 c 9 9
;
run;
/* var lists */
%let svars = v1 v2;
%let qvars = %sysfunc(transtrn(%str( )&svars,%str( ),%str( )q_));
/* for each obs in s, loops over the id-matching q observations */
data nonmatches;
/* load q data in hash */
if _n_ = 1 then do;
if 0 then set q;
dcl hash h(dataset:'q', multidata:'y');
h.defineKey('id');
h.defineData(all:'y');
h.defineDone();
end;
retain OK 0;
/* for each obs in s */
set s;
array qarr
array sarr
rc = h.find();
do while (rc = OK);
if lang = q_lang then do i = 1 to dim(qarr);
if qarr ^= sarr then do;
var = vname(sarr);
qval = qarr;
sval = sarr;
output;
end;
end;
rc = h.find_next();
end;
keep id lang var qval sval;
run;
/* check */
proc print data=nonmatches noobs;
run;
/* on lst
id lang var qval sval
2 b v2 4 7
2 b v1 5 3
2 b v2 6 7
*/
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!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: