Inserting observation to a dataset using PROC SQL

[Altal]

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

[RW9]

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;

[Altal]

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

[RW9]

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;

[RamKumar]

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?

[RW9]

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

[Altal]

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

[Tom]

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;

[Altal]

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.

[Tom · Accepted solution]

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

;

[Altal]

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

[Tom]

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

put(compDate,mmddyy10.)