Please help with this problem. I have two sets of SAS tables. Table 1 called raw data has the following information, as an example. Student ID 123 124 125 126 127 Table 2 called ‘inputted data’ has same information as Table 1 (student ID) but more is added; Student ID student name class 123 john 6 124 Tom 7 125 mark 8 126 matt 9 121 andrew 10 Now I am interested in whether the data inputter on table 2 has interred student ID correctly, which is clear on Andrew’s name, ID ‘121’ does not exist on table one. I want to create a table as below using some sort of if function/ lookup which scans table 1 and tells me which student ID has been entered incorrectly like below; Table 3 Student ID student name class correct ID? 123 john 6 yes 124 peter 7 yes 125 mark 8 yes 126 matt 9 yes 121 andrew 10 ‘ID incorrect’ Many thanks for your help.
hi ... one idea (there are lots of ways to do this) ...
data goodids (index=(id));
input id @@;
datalines;
123 124 125 126 127
;
data students;
input id name :$10. class;
set goodids key=id / unique;
if _error_ then do;
correctid = 0;
_error_ = 0;
end;
else correctid = 1;
datalines;
123 john 6
124 Tom 7
125 mark 8
126 matt 9
121 andrew 10
;
id name class correctid
123 john 6 1
124 Tom 7 1
125 mark 8 1
126 matt 9 1
121 andrew 10 0
Please help with this problem. I have two sets of SAS tables. Table 1 called raw data has the following information, as an example.
Student ID
123
124
125
126
127
Table 2 called ‘inputted data’ has same information as Table 1 (student ID) but more is added;
Student ID student name class
123 john 6
124 tom 7
125 mark 8
126 matt 9
121 andrew 10
Now I am interesed if the data inputter on table 2 has intered the student ID correctly which is clear on Andrews student ID of '121' which does not exist on table 1.I want to create table3 as below with a new variable 'Correct ID' which can tell me this by scanning all id values on table1 and returning the status using a combination of if/lookup techniques
student ID student name Class Correct ID?
123 john 6 yes
124 tom 7 yes
125 mark 8 yes
126 matt 9 yes
121 andrew 10 'ID incorrect'
Thanks very much.
data want ;
merge raw(in=inraw) new ;
by studentid;
correctid = inraw;
run;
This will set variable correctid to 1 (true) if the studentid value was in the RAW dataset. Otherwise it will be 0 (false);
You could also use an IF statement to create correctid variable as a character variable instead.
A SQL approach:
data h1;
input ID$;
cards;
123
124
125
126
127
;
data h2;
input (ID name class) ($);
cards;
123 john 6
124 tom 7
125 mark 8
126 matt 9
121 andrew 10
;
proc sql;
select * , case when id in (select id from h1) then 'correct' else 'incorrect' end as correctness from h2;quit;
Haikuo
Hai. Kuo and all thanks very much for your answers.Haikuo your approach is the most direct.I run your code but the results i.e final file appears as an SQL output. But I want this final result to appear in the 'work' folder just as initial data h1 and data h2 appears (so hat i can peform further checks). How do I do this?I am not familiar with SQL.
proc sql;
create table want as
select * , case when id in (select id from h1) then 'correct' else 'incorrect' end as correctness from h2;quit;
Haikuo
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.