Please help with this simple problem

[zetter]

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.

[MikeZdeb]

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

[zetter]

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.

[Tom]

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.

[Haikuo]

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

[zetter]

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.

[Haikuo]

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