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

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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

7 REPLIES 7
Jagadishkatam
Amethyst | Level 16

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

 

 

dina_d
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

dina_d
Obsidian | Level 7

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!!!

arpitagarwal512
Calcite | Level 5

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;

dina_d
Obsidian | Level 7
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. -##

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 1589 views
  • 3 likes
  • 4 in conversation