Is there a PROC SQL way to look at two different tables (would it be a join? ) and based on speciic criterias create a flag in the main dataset?
See table example below- the bottom table would be the main dataset:
Notice student E902 reported in GATE (8) by two different schools but flagged once with no new record to be produced. Can anyone help me? Thank you in advance
STUDENT | GRADE | SCHOOL PROGRAM | |||
E902 | 9 | GARDEN | 8 | ||
E902 | 9 | GARDEN | 9 | ||
E902 | 9 | BELL | 8 | ||
N233 | 9 | BELL | 8 | ||
E981 | 10 | DINGLE | 9 | ||
STUDENT | GRADE | SCHOOL | GATE | BASP | ADVCALC |
N233 | 9 | BELL | X | ||
E981 | 10 | DINGLE | X | ||
E902 | 9 | GARDEN | X | X | |
A format, a view and transpose :
proc format;
value pgm
8 = GATE
9 = BASP
1 = ADVCALC
2 = BIOSC;
run;
data have;
input student $ grade school $ program;
datalines;
E902 9 GARDEN 8
E902 9 GARDEN 9
E902 9 BELL 8
N233 9 BELL 8
E981 10 DINGLE 9
;
proc sql;
create view havev as
select student, grade, school, program format=pgm., "X" as x length=1
from have;
quit;
proc transpose data=havev out=want(drop=_name_);
by student grade school notsorted;
id program;
var x;
run;
proc print; run;
PG
SAS_new wrote:
Is there a PROC SQL way to look at two different tables (would it be a join? ) and based on speciic criterias create a flag in the main dataset?
See table example below- the bottom table would be the main dataset:
Notice student E902 reported in GATE (8) by two different schools but flagged once with no new record to be produced. Can anyone help me? Thank you in advance
Yes, you can look at two different tables, using joins or sub-queries.
Can anyone help you?
I don't think you've provided enough information yet.
What is your criteria for setting the flag? What does your data look like? What does the other two datasets look like? What do you want the final dataset to look like?
Do you have to use Proc SQL or can you use datasteps?
Thank you-
I think my mind was going faster than my fingers. I guess I am trying to link table 1 with table 2(translation) and then I would create three new columns (GATE, BASP, ADVCALC, BIOS) in table 3. If table 1 contained program = 8, mark "X" under column GATE in table 3. If program= 9 in table 1, Mark "X" for BASP in table 3 and if program=1 in table 1, mark "X" under ADVCALC in table 3. I would link by student, grade and school. PROC SQL not required- if data step will take care of it. Thank you
Here is the first table:
STUDENT | GRADE | SCHOOL PROGRAM | ||
E902 | 9 | GARDEN | 8 | |
E902 | 9 | GARDEN | 9 | |
E902 | 9 | BELL | 8 | |
N233 | 9 | BELL | 8 | |
E981 | 10 | DINGLE | 9 |
Second table:
PROGRAM CODE | PROGRAM | ||
8 | GATE | ||
9 | BASP | ||
1 | ADVCALC | ||
2 | BIOSC |
Would like my results to look like this:
STUDENT | GRADE | SCHOOL | GATE | BASP | ADVCALC |
N233 | 9 | BELL | X | ||
E981 | 10 | DINGLE | X | ||
E902 | 9 | GARDEN | X | X | |
proc sql noprint;
create table table3 as
select
t1.student,
t1.grade,
t1.school,
max(case when t2.program='GATE' then 'X' end) as GATE,
max(case when t2.program='BASP' then 'X' end) as BASP,
max(case when t2.program='ADVCALC' then 'X' end) as ADVCALC
from
table1 t1
left outer join table2 t2
on t1.progam=t2.program_code
group by
t1.student, t1.grade, t1.school;
quit;
A format, a view and transpose :
proc format;
value pgm
8 = GATE
9 = BASP
1 = ADVCALC
2 = BIOSC;
run;
data have;
input student $ grade school $ program;
datalines;
E902 9 GARDEN 8
E902 9 GARDEN 9
E902 9 BELL 8
N233 9 BELL 8
E981 10 DINGLE 9
;
proc sql;
create view havev as
select student, grade, school, program format=pgm., "X" as x length=1
from have;
quit;
proc transpose data=havev out=want(drop=_name_);
by student grade school notsorted;
id program;
var x;
run;
proc print; run;
PG
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.