BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SAS_new
Calcite | Level 5

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

STUDENTGRADESCHOOL PROGRAM
E9029GARDEN8
E9029GARDEN9
E9029BELL8
N2339BELL8
E98110DINGLE9
STUDENTGRADESCHOOLGATEBASPADVCALC
N2339BELLX
E98110DINGLE X
E9029GARDENXX
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

PG

View solution in original post

4 REPLIES 4
Reeza
Super User

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?

SAS_new
Calcite | Level 5

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

GRADESCHOOL PROGRAM
E9029GARDEN8
E9029GARDEN9
E9029BELL8
N2339BELL8
E98110DINGLE9

Second table:

PROGRAM

CODE

PROGRAM
8 GATE
9BASP
1ADVCALC
2BIOSC

Would like my results to look like this:

STUDENTGRADESCHOOLGATEBASPADVCALC
N2339BELLX
E98110DINGLEX
E9029GARDENXX
 
DBailey
Lapis Lazuli | Level 10

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;

PGStats
Opal | Level 21

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

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 890 views
  • 5 likes
  • 4 in conversation