topic Re: Please help with this simple problem in SAS Procedures

Please help with this simple problem

zetter — Tue, 10 Jul 2012 15:22:50 GMT

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.

Re: Please help with this simple problem

MikeZdeb — Tue, 10 Jul 2012 15:35:52 GMT

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

Re: Please help with this simple problem

zetter — Tue, 10 Jul 2012 15:41:13 GMT

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.

Re: Please help with this simple problem

Tom — Tue, 10 Jul 2012 16:01:20 GMT

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.

Re: Please help with this simple problem

Haikuo — Tue, 10 Jul 2012 16:15:54 GMT

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

Re: Please help with this simple problem

zetter — Tue, 10 Jul 2012 19:09:13 GMT

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.

Re: Please help with this simple problem

Haikuo — Tue, 10 Jul 2012 19:13:32 GMT

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