DATA Step, Macro, Functions and more

proc sql problem adding new variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

proc sql problem adding new variable

Hello,
I am trying to use proc scq in order to add a new variable to existing data set, the new variable gives condition name for each observation. My next move will be to find average reaction times for each condition for each subject. The data set I have contains participant number (numeric), reaction times (numeric) and 4 variables (2string and 2 numeric) coding level of each corresponding factor. Each condition is produced by a combination of 4 variables. Below is a code which (I hope) is supposed to make a table, where I will have old table + a column "Cond". This column will have one of 16 condition names (string) in each line.

proc sql;create table step2.w as
select visibility, soa, globality, prime, sub, rt,
case
when prime='NP' && globality='G' && soa=2 && visibility=0 then Cond='NpG0s2'
when prime='OP' && globality='G' && soa=2 && visibility=0 then Cond='OpG0s2'
when prime='NP' && globality='G' && soa=4 && visibility=0 then Cond='NpG0s4' 
when prime='OP' && globality='G' && soa=4 && visibility=0 then Cond='OpG0s4'
when prime='NP' && globality='G' && soa=2 && visibility=1 then Cond='NpG1s2'
when prime='OP' && globality='G' && soa=2 && visibility=1 then Cond='OpG1s2'
when prime='NP' && globality='G' && soa=4 && visibility=1 then Cond='NpG1s4'
when prime='OP' && globality='G' && soa=4 && visibility=1 then Cond='OpG1s4'
when prime='NP' && globality='G' && soa=2 && visibility=2 then Cond='NpG2s2'
when prime='OP' && globality='G' && soa=2 && visibility=2 then Cond='OpG2s2'
when prime='NP' && globality='G' && soa=4 && visibility=2 then Cond='NpG2s4'
when prime='OP' && globality='G' && soa=4 && visibility=2 then Cond='OpG2s4'
when prime='NP' && globality='G' && soa=2 && visibility=3 then Cond='NpG3s2'
when prime='OP' && globality='G' && soa=2 && visibility=3 then Cond='OpG3s2'
when prime='NP' && globality='G' && soa=4 && visibility=3 then Cond='NpG3s4' 
when prime='OP' && globality='G' && soa=4 && visibility=3 then Cond='OpG3s4'
else .
end as Cond
from step2.Clean;
quit;

However, I receive an error message in the log: "ERROR: The following columns were not found in the contributing tables: Cond."

I know that there is no such variable in the source table "step2.Clean". I want to create it and add it. Could you, please, tell me what am I doing wrong?

I will greatly appreciate your feedback and time,

Dina.

 

 


Accepted Solutions
Solution
‎05-25-2017 05:13 AM
Trusted Advisor
Posts: 1,137

Re: proc sql problem adding new variable

[ Edited ]

Try the below code and let me know if it helps resolve the issue

 



proc sql;create table step2.w as select visibility, soa, globality, prime, sub, rt, case when prime='NP' && globality='G' && soa=2 && visibility=0 then 'NpG0s2' when prime='OP' && globality='G' && soa=2 && visibility=0 then 'OpG0s2' when prime='NP' && globality='G' && soa=4 && visibility=0 then 'NpG0s4' when prime='OP' && globality='G' && soa=4 && visibility=0 then 'OpG0s4' when prime='NP' && globality='G' && soa=2 && visibility=1 then 'NpG1s2' when prime='OP' && globality='G' && soa=2 && visibility=1 then 'OpG1s2' when prime='NP' && globality='G' && soa=4 && visibility=1 then 'NpG1s4' when prime='OP' && globality='G' && soa=4 && visibility=1 then 'OpG1s4' when prime='NP' && globality='G' && soa=2 && visibility=2 then 'NpG2s2' when prime='OP' && globality='G' && soa=2 && visibility=2 then 'OpG2s2' when prime='NP' && globality='G' && soa=4 && visibility=2 then 'NpG2s4' when prime='OP' && globality='G' && soa=4 && visibility=2 then 'OpG2s4' when prime='NP' && globality='G' && soa=2 && visibility=3 then 'NpG3s2' when prime='OP' && globality='G' && soa=2 && visibility=3 then 'OpG3s2' when prime='NP' && globality='G' && soa=4 && visibility=3 then 'NpG3s4' when prime='OP' && globality='G' && soa=4 && visibility=3 then 'OpG3s4' else '' end as Cond from step2.Clean; quit;

 

Thanks,
Jag

View solution in original post


All Replies
Solution
‎05-25-2017 05:13 AM
Trusted Advisor
Posts: 1,137

Re: proc sql problem adding new variable

[ Edited ]

Try the below code and let me know if it helps resolve the issue

 



proc sql;create table step2.w as select visibility, soa, globality, prime, sub, rt, case when prime='NP' && globality='G' && soa=2 && visibility=0 then 'NpG0s2' when prime='OP' && globality='G' && soa=2 && visibility=0 then 'OpG0s2' when prime='NP' && globality='G' && soa=4 && visibility=0 then 'NpG0s4' when prime='OP' && globality='G' && soa=4 && visibility=0 then 'OpG0s4' when prime='NP' && globality='G' && soa=2 && visibility=1 then 'NpG1s2' when prime='OP' && globality='G' && soa=2 && visibility=1 then 'OpG1s2' when prime='NP' && globality='G' && soa=4 && visibility=1 then 'NpG1s4' when prime='OP' && globality='G' && soa=4 && visibility=1 then 'OpG1s4' when prime='NP' && globality='G' && soa=2 && visibility=2 then 'NpG2s2' when prime='OP' && globality='G' && soa=2 && visibility=2 then 'OpG2s2' when prime='NP' && globality='G' && soa=4 && visibility=2 then 'NpG2s4' when prime='OP' && globality='G' && soa=4 && visibility=2 then 'OpG2s4' when prime='NP' && globality='G' && soa=2 && visibility=3 then 'NpG3s2' when prime='OP' && globality='G' && soa=2 && visibility=3 then 'OpG3s2' when prime='NP' && globality='G' && soa=4 && visibility=3 then 'NpG3s4' when prime='OP' && globality='G' && soa=4 && visibility=3 then 'OpG3s4' else '' end as Cond from step2.Clean; quit;

 

Thanks,
Jag
Super User
Super User
Posts: 7,997

Re: proc sql problem adding new variable

[ Edited ]

Simpler one step one line of code approach:

Datastep:

data step2.w;
  set step2.clean;
  cond=cats(prime,"G",put(visibility,best.),"S",put(soa,best.));
run;

 

Case in SQL is not the same as if then.in SAS.  The construct for this is:

case <condition> then <result> else <result> end as <variable>

 

You have put cond= statements in where there shouldn't be any, you first tow rows:

when prime='NP' && globality='G' && soa=2 && visibility=0 then 'NpG0s2'
when prime='OP' && globality='G' && soa=2 && visibility=0 then 'OpG0s2'

Also, why do this in SQL?  A datastep with select() or nested ifs would be far simpler to read.  Even changing your code slightly would make it more readble:

case when prime="NP"
   case when glabality="G" 
     case when ...
   else
      ...
   end
else 
  ...
end as COND

 

 

Occasional Contributor
Posts: 9

Re: proc sql problem adding new variable

Hi and thank you very much for the reply. I tried the change proposed by Jag and somehow it worked. 

I need to read about "cats", because I am not sure I understand what happens in brackets. 

Thank you so much!

Dina.

Super User
Super User
Posts: 7,997

Re: proc sql problem adding new variable

It is a function.  Its purpose is to concatenate various strings together.  What you are doing with that big block of code is concatenating the data from prime, with visibility and soa, with some other characters in between.  Doing it your way oever is a big chunk of code, hence we use functions to simplify out code (and make it more efficient as these are compiled).

 

  cond=cats(prime,"G",put(visibility,best.),"S",put(soa,best.));

So prime is the first character parameter, "G" is the second, etc. and these all get concatenated into one long string and then put in cond. 

I assume you have come form DB pogramming or something, whilst SQL is quite useful in certain circumstances, if your going to use SAS I would really recommned you learn SAS - i.e. the datastep language - as from the above you can save yourself a page of code just by knowing some basics.

Occasional Contributor
Posts: 9

Re: proc sql problem adding new variable

Wow, this also works!

data step2.Z; set step2.Clean;
cond=cats(prime,globality,put(visibility,best.),put(soa,best.));
keep sub rt Cond;
run;

Many thanks!!!

Occasional Contributor
Posts: 7

Re: proc sql problem adding new variable

please run below one:

 

proc sql;create table step2.w as
select visibility, soa, globality, prime, sub, rt,
case
when prime='NP' && globality='G' && soa=2 && visibility=0 then 'NpG0s2'
when prime='OP' && globality='G' && soa=2 && visibility=0 then 'OpG0s2'
when prime='NP' && globality='G' && soa=4 && visibility=0 then 'NpG0s4'
when prime='OP' && globality='G' && soa=4 && visibility=0 then 'OpG0s4'
when prime='NP' && globality='G' && soa=2 && visibility=1 then 'NpG1s2'
when prime='OP' && globality='G' && soa=2 && visibility=1 then 'OpG1s2'
when prime='NP' && globality='G' && soa=4 && visibility=1 then 'NpG1s4'
when prime='OP' && globality='G' && soa=4 && visibility=1 then 'OpG1s4'
when prime='NP' && globality='G' && soa=2 && visibility=2 then 'NpG2s2'
when prime='OP' && globality='G' && soa=2 && visibility=2 then 'OpG2s2'
when prime='NP' && globality='G' && soa=4 && visibility=2 then 'NpG2s4'
when prime='OP' && globality='G' && soa=4 && visibility=2 then 'OpG2s4'
when prime='NP' && globality='G' && soa=2 && visibility=3 then 'NpG3s2'
when prime='OP' && globality='G' && soa=2 && visibility=3 then 'OpG3s2'
when prime='NP' && globality='G' && soa=4 && visibility=3 then 'NpG3s4'
when prime='OP' && globality='G' && soa=4 && visibility=3 then 'OpG3s4'
else .
end as Cond
from step2.Clean;
quit;

Occasional Contributor
Posts: 9

Re: proc sql problem adding new variable

Posted in reply to arpitagarwal512
Hi and sorry for the delay.
No, I got this "ERROR: Result of WHEN clause 17 is not the same data type
as the preceding results." Only this ending (suggested here before) worked:
else ''
end as Cond
from step2.Clean;
quit

##- Please type your reply above this line. Simple formatting, no
attachments. -##
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 216 views
  • 3 likes
  • 4 in conversation