Inserting observation to a dataset using PROC SQL

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

Inserting observation to a dataset using PROC SQL

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


Accepted Solutions
Solution
‎09-25-2014 10:05 AM
Super User
Super User
Posts: 6,364

Re: Inserting observation to a dataset using PROC SQL

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

;

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Inserting observation to a dataset using PROC SQL

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;

Contributor
Posts: 62

Re: Inserting observation to a dataset using PROC SQL

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Inserting observation to a dataset using PROC SQL

Ok, (pretend I am Roy for a bit :smileyshockedSmiley Happy

proc sql;

     insert into PROBLEM

     select      ID,

                    DATE,

                    "Completed="||strip(COMPLETED)||" Dropped="||strip(DROPPED) as NOTE

     from        HAVE;

quit;

Regular Contributor
Posts: 165

Re: Inserting observation to a dataset using PROC SQL

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?

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Inserting observation to a dataset using PROC SQL

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

Contributor
Posts: 62

Re: Inserting observation to a dataset using PROC SQL

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

Super User
Super User
Posts: 6,364

Re: Inserting observation to a dataset using PROC SQL

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;

Contributor
Posts: 62

Re: Inserting observation to a dataset using PROC SQL

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.

Solution
‎09-25-2014 10:05 AM
Super User
Super User
Posts: 6,364

Re: Inserting observation to a dataset using PROC SQL

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

;

Contributor
Posts: 62

Re: Inserting observation to a dataset using PROC SQL

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

Super User
Super User
Posts: 6,364

Re: Inserting observation to a dataset using PROC SQL

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

put(compDate,mmddyy10.)

☑ This topic is SOLVED.

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

Discussion stats
  • 11 replies
  • 1489 views
  • 6 likes
  • 4 in conversation