Help using Base SAS procedures

Please help with this simple problem

Reply
Contributor
Posts: 36

Please help with this simple problem

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.

Valued Guide
Posts: 765

Re: Please help with this simple problem

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

Contributor
Posts: 36

Re: Please help with this simple problem

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.

Super User
Super User
Posts: 7,076

Re: Please help with this simple problem

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.

Respected Advisor
Posts: 3,156

Re: Please help with this simple problem

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

Contributor
Posts: 36

Re: Please help with this simple problem

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.

Respected Advisor
Posts: 3,156

Re: Please help with this simple problem

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

Ask a Question
Discussion stats
  • 6 replies
  • 243 views
  • 3 likes
  • 4 in conversation