Hi Everyone,
I have a dataset (named "problems") with few variables ID, date, Note
I'd like to run PROC SQL to query another database dataset and based on that query I update (problems) from the query I run, so that ID = ID from the query, Date = date from the query, and Note = (some other data pulled from the query in the form of "Complete=yes/no Dropped=yes/not") with Complete, and Dropped being two separate variables in the database
for example:
ID Date Note
---------------------------
1 1/1/2014 Complete=yes Dropped =no
2 2/6/2014 Complete=n Dropped =no
having trouble with syntax
plz help...
If you are writing an SQL insert statement you should list the variables that you are inserting into. This will solve issue of trying to provide values for only some of the variables. It will also allow you to not care what order the variables are on the table.
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002294531.htm
insert into problems (id,date,note)
select id,date,catx(' ',catx('=','Completed',completed),catx('=','Dropped',dropped))
from HAVE
;
Something like (note its really a bit of guess as you haven't supplied much information):
proc sql;
create table WANT as
select COALESCE(A.ID,B.ID) as ID,
COALESCE(A.DATE,B.DATE) as DATE,
"Complete="||case when A.VAL is not null then "yes" else "no" end||
" Dropped="||case when B.VAL is not null then "yes" else "no" end as NOTE
from DATA1 A
full join DATA2 B
on A.ID=B.ID
and A.DATE=B.DATE;
quit;
Thanks Roy - there is no join required; "Problem" is an empty dataset, that I want to update along the program (using INSERT statement in proc sql) by looking into other dataset and doing some queries
ID Date Completed Dropped
1 1/1/2014 yes yes
2 1/6/2014 yes no
3 3/6/2014 yes yes
.
.
.
so I just want to query the data above and insert these into an existing data called "problem" that has 3 variables (ID, Date, Note)
so that the output would be
ID Date Note
1 1/1/2014 Completed=yes Dropped=yes
2 1/6/2014 Completed=yes Dropped=no
3 3/6/2014 Completed=yes Dropped=yes
hope this explains it better..
Ok, (pretend I am Roy for a bit :smileyshocked:)
proc sql;
insert into PROBLEM
select ID,
DATE,
"Completed="||strip(COMPLETED)||" Dropped="||strip(DROPPED) as NOTE
from HAVE;
quit;
why we've used '||' operator whilst calculating the variable completed?
"Completed="||strip(COMPLETED)||" Dropped="||strip(DROPPED) as NOTE
Also you've used two '||' for the first strip function and one || for the second strip function. Why so?
|| means concatenate the string on the left of the operator to the string on the right of the operator, the operator being ||. So I want to concatenate "Completed=" and the variable completed, and the result of than concatenated with " Dropped=" and then concatenated with the variable DROPPED. So three concatenators for four items.
Sorry, RW9, don't know why had the impression you're Roy ><
You could of course just use normal SAS steps instead of SQL.
data have ;
length id date 8 completed dropped $3.;
informat date mmddyy10.;
format date date9.;
input id date completed dropped;
cards;
1 1/1/2014 yes yes
2 1/6/2014 yes no
3 3/6/2014 yes yes
;
data to_insert / view=to_insert;
set have ;
length note $50 ;
note = catx(' ',catx('=','Completed',completed),catx('=','Dropped',dropped));
keep id date note;
run;
proc append data=to_insert base=problems;
run;
Thanks to you guys,
Tom is there away to circumvent this error, without inserting same number of variables in the dataset?
ERROR: Attempt to insert fewer columns than specified after the INSERT table name.
If you are writing an SQL insert statement you should list the variables that you are inserting into. This will solve issue of trying to provide values for only some of the variables. It will also allow you to not care what order the variables are on the table.
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002294531.htm
insert into problems (id,date,note)
select id,date,catx(' ',catx('=','Completed',completed),catx('=','Dropped',dropped))
from HAVE
;
Thank you Tom this is very helpful...
I added one more variable (a date variable) to the last inside the CATX, but it shows as number 19389 for example, how can I put is as a date (mmddyy10.)?
catx(' ',catx('=','Completed',completed),catx('=','Dropped',dropped), catx('=','Completion Date',compDate))
Just replace the reference to the variable with this function call.
put(compDate,mmddyy10.)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.