topic Re: Inserting observation to a dataset using PROC SQL in SAS Programming

Inserting observation to a dataset using PROC SQL

Altal — Thu, 25 Sep 2014 11:52:54 GMT

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

Re: Inserting observation to a dataset using PROC SQL

RW9 — Thu, 25 Sep 2014 12:24:51 GMT

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;

Re: Inserting observation to a dataset using PROC SQL

Altal — Thu, 25 Sep 2014 12:38:03 GMT

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

Re: Inserting observation to a dataset using PROC SQL

RW9 — Thu, 25 Sep 2014 12:45:17 GMT

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;

Re: Inserting observation to a dataset using PROC SQL

Tom — Thu, 25 Sep 2014 13:38:22 GMT

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;

Re: Inserting observation to a dataset using PROC SQL

RamKumar — Thu, 25 Sep 2014 13:38:48 GMT

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?

Re: Inserting observation to a dataset using PROC SQL

Altal — Thu, 25 Sep 2014 13:41:24 GMT

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.

Re: Inserting observation to a dataset using PROC SQL

RW9 — Thu, 25 Sep 2014 13:54:47 GMT

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

Re: Inserting observation to a dataset using PROC SQL

Tom — Thu, 25 Sep 2014 14:05:09 GMT

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

;

Re: Inserting observation to a dataset using PROC SQL

Altal — Thu, 25 Sep 2014 14:19:31 GMT

Sorry, RW9, don't know why had the impression you're Roy ><

Re: Inserting observation to a dataset using PROC SQL

Altal — Thu, 25 Sep 2014 14:22:01 GMT

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

Re: Inserting observation to a dataset using PROC SQL

Tom — Thu, 25 Sep 2014 14:25:17 GMT

Just replace the reference to the variable with this function call.

put(compDate,mmddyy10.)