topic Re: problem importing list and using in proc sql in SAS Programming

problem importing list and using in proc sql

dan999 — Thu, 29 May 2014 15:30:20 GMT

I'm trying to import a list of codes to use in a proc sql query but what I'm
doing isn't working. I've tried these two methods. The list seems to be imported okay

but when I try to use it in the proc sql it doesn't work.

proc import
datafile="&path./&sysuserid/&custrun./asgnroletypid.csv"
out=asgnroletypid dbms=csv;
run;

proc sql;
select compress("'"||roletypid||"'") into :roletypid separated by ","
from asgnroletypid
;
quit;
%put role &roletypid.;

data roletypid;
infile "&path./&sysuserid/&custrun./asgnroletypid.csv" MISSOVER;
input roletypid :$4.;
roletypid=TRIM(LEFT(UPCASE(COMPRESS(COMPRESS(roletypid,"', "),'"'))));
run;
proc sql noprint;
Select compress("'"||roletypid||"'") into :roletypid separated by ","
From roletypid ;
Quit;
%put roletypid &roletypid.;

but when I use the list in a query it doesn't work

proc sql;
create table tmphsc1 as
select *
from tmphsc
WHERE actv_chgusr_role IN (&roletypid.)
and mbr_id = 16991
;
run;

If I create the list like this it will work.

data roletypid;
infile datalines;
input roletypid $;
return;
datalines;
MD
RMD
MDS
PH
PHC
PHCMP
CMP
;
run;

proc sql;

select compress("'"||roletypid||"'") into roletypid separated by ","

from roletypid

;

quit;

%put roletypid &roletypid.;

Any suggestions? Thanks.

Re: problem importing list and using in proc sql

ballardw — Thu, 29 May 2014 15:41:20 GMT

You don't show us what the value of &Roletypid looks like. Also, what is meant by "doesn't work"? No results, unexpected results, error or something else.

Have you tried looking at the code generated using options mprint symbolgen; before the proc that doesn't work?

Re: problem importing list and using in proc sql

Reeza — Thu, 29 May 2014 16:34:29 GMT

Post your log. Your last function is missing the colon, :, so I'm not sure why it works, it should only have one value.

You should also look into the quote function rather than using the || and quotation marks, it may be embedding extra spaces.

Again, hard to define without seeing.

proc sql;

select quote(compress(roletypid)) into :roletypid separated by ","

from asgnroletypid

;

quit;

Re: problem importing list and using in proc sql

dan999 — Thu, 29 May 2014 18:05:55 GMT

The imported string would look like this - 'MD','RMD','MDS','PH','PHC','PHCMP','CMP'. I'll look into using the quote function. Thanks.

Re: problem importing list and using in proc sql

dan999 — Thu, 29 May 2014 18:07:44 GMT

Log below.

Project Log turned on at 5/29/2014 12:58:14 PM
Log for "Program" run at 5/29/2014 12:58:30 PM on SASCSGPrdb
1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROJECTPATH='';
5          %LET _CLIENTPROJECTNAME='';
6          %LET _SASPROGRAMFILE=;
7
8          ODS _ALL_ CLOSE;
9          OPTIONS DEV=ACTIVEX;
10         GOPTIONS XPIXELS=0 YPIXELS=0;
11         FILENAME EGSR TEMP;
12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue
12       ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS%209.3.0%20(SAS%20BI%20Solutions)/x86/SASEnterpriseGuide/5.1/Styles/HtmlBl
12       ! ue.css") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
13
14         GOPTIONS ACCESSIBLE;
15         proc import
16          datafile="&path./&sysuserid/&custrun./asgnroletypid.csv"
17          out=asgnroletypid dbms=csv;
18         run;

NOTE: Import cancelled. Output dataset WORK.ASGNROLETYPID already exists. Specify REPLACE option to overwrite it.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              31.15k
      OS Memory           21032.00k
      Timestamp           05/29/2014 12:58:27 PM
      Page Faults                       0
      Page Reclaims                     2
      Page Swaps                        0
      Voluntary Context Switches        0
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0

19

20         proc sql;
21          select compress("'"||roletypid||"'") into :roletypid separated by ","
22          from asgnroletypid
23         ;
24         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              704.68k
      OS Memory           21544.00k
      Timestamp           05/29/2014 12:58:27 PM
      Page Faults                       0
      Page Reclaims                     126
      Page Swaps                        0
      Voluntary Context Switches        0
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0

25         %put role &roletypid.;
role 'MD','RMD','MDS','PH','PHC','PHCMP','CMP'
26         proc sql;
27          create table tmphsc1 as
28          select *
29          from tmphsc
30         /* WHERE actv_chgusr_role IN ('MD','RMD','MDS','PH','PHC','PHCMP','CMP')*/
31          WHERE actv_chgusr_role IN (&roletypid.)
32          and mbr_id = 16991
33         ;
NOTE: Table WORK.TMPHSC1 created, with 0 rows and 16 columns.

34         run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
35
36         GOPTIONS NOACCESSIBLE;
37         %LET _CLIENTTASKLABEL=;
38         %LET _CLIENTPROJECTPATH=;
39         %LET _CLIENTPROJECTNAME=;
40         %LET _SASPROGRAMFILE=;
41
42         ;*';*";*/;quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              568.18k
      OS Memory           21544.00k
      Timestamp           05/29/2014 12:58:27 PM
      Page Faults                       10
      Page Reclaims                     35
      Page Swaps                        0
      Voluntary Context Switches        15
      Involuntary Context Switches      1
      Block Input Operations            0
      Block Output Operations           0

42       !                run;

43         ODS _ALL_ CLOSE;
44
45
46         QUIT; RUN;
47
Log for "Program" run at 5/29/2014 1:00:35 PM on SASCSGPrdb
1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROJECTPATH='';
5          %LET _CLIENTPROJECTNAME='';
6          %LET _SASPROGRAMFILE=;
7
8          ODS _ALL_ CLOSE;
9          OPTIONS DEV=ACTIVEX;
10         GOPTIONS XPIXELS=0 YPIXELS=0;
11         FILENAME EGSR TEMP;
12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue
12       ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS%209.3.0%20(SAS%20BI%20Solutions)/x86/SASEnterpriseGuide/5.1/Styles/HtmlBl
12       ! ue.css") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
13
14         GOPTIONS ACCESSIBLE;
15         proc import
16          datafile="&path./&sysuserid/&custrun./asgnroletypid.csv"
17          out=asgnroletypid9 dbms=csv;
18         run;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
19          /**********************************************************************
20          *   PRODUCT:   SAS
21          *   VERSION:   9.3
22          *   CREATOR:   External File Interface
23          *   DATE:      29MAY14
24          *   DESC:      Generated SAS Datastep Code
25          *   TEMPLATE SOURCE: (None Specified.)
26          ***********************************************************************/
27             data WORK.ASGNROLETYPID9    ;
28             %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
29             infile '/sasprdb/optum/rad/users/ddonah6/medtime2touch/asgnroletypid.csv' delimiter = ',' MISSOVER DSD lrecl=32767
29       ! firstobs=2 ;
30                informat roletypid $6. ;
31                format roletypid $6. ;
32             input
33                         roletypid $
34             ;
35             if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
36             run;

NOTE: The infile '/sasprdb/optum/rad/users/ddonah6/medtime2touch/asgnroletypid.csv' is:
      Filename=/sasprdb/optum/rad/users/ddonah6/medtime2touch/asgnroletypid.csv,
      Owner Name=ddonah6,Group Name=ioprad,
      Access Permission=rw-r--r--,
      Last Modified=Wed May 28 15:36:51 2014,
      File Size (bytes)=46

NOTE: 7 records were read from the infile '/sasprdb/optum/rad/users/ddonah6/medtime2touch/asgnroletypid.csv'.
      The minimum record length was 3.
      The maximum record length was 6.
NOTE: The data set WORK.ASGNROLETYPID9 has 7 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              6219.29k
      OS Memory           24372.00k
      Timestamp           05/29/2014 01:00:31 PM
      Page Faults                       0
      Page Reclaims                     74
      Page Swaps                        0
      Voluntary Context Switches        16
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0

7 rows created in WORK.ASGNROLETYPID9 from /sasprdb/optum/rad/users/ddonah6/medtime2touch/asgnroletypid.csv.

NOTE: WORK.ASGNROLETYPID9 data set was successfully created.
NOTE: The data set WORK.ASGNROLETYPID9 has 7 observations and 1 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.09 seconds
      user cpu time       0.03 seconds
      system cpu time     0.01 seconds
      memory              6219.29k
      OS Memory           24372.00k
      Timestamp           05/29/2014 01:00:31 PM
      Page Faults                       9
      Page Reclaims                     779
      Page Swaps                        0
      Voluntary Context Switches        61
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0

37
38         proc sql;
39          select compress("'"||roletypid||"'") into :roletypid separated by ","
40          from asgnroletypid9
41         ;
42         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              705.31k
      OS Memory           22312.00k
      Timestamp           05/29/2014 01:00:31 PM
      Page Faults                       0
      Page Reclaims                     28
      Page Swaps                        0
      Voluntary Context Switches        0
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0

43         %put role &roletypid.;
role 'MD','RMD','MDS','PH','PHC','PHCMP','CMP'
44         proc sql;
45          create table tmphsc1 as
46          select *
47          from tmphsc
48         /* WHERE actv_chgusr_role IN ('MD','RMD','MDS','PH','PHC','PHCMP','CMP')*/
49          WHERE actv_chgusr_role IN (&roletypid.)
50          and mbr_id = 16991
51         ;
NOTE: Table WORK.TMPHSC1 created, with 0 rows and 16 columns.

52         run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
53
54         GOPTIONS NOACCESSIBLE;
55         %LET _CLIENTTASKLABEL=;
56         %LET _CLIENTPROJECTPATH=;
57         %LET _CLIENTPROJECTNAME=;
58         %LET _SASPROGRAMFILE=;
59
60         ;*';*";*/;quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              567.59k
      OS Memory           22312.00k
      Timestamp           05/29/2014 01:00:31 PM
      Page Faults                       9
      Page Reclaims                     23
      Page Swaps                        0
      Voluntary Context Switches        16
      Involuntary Context Switches      1
      Block Input Operations            0
      Block Output Operations           0

60       !                run;

61         ODS _ALL_ CLOSE;
62
63
64         QUIT; RUN;
65

Re: problem importing list and using in proc sql

dan999 — Thu, 29 May 2014 18:16:28 GMT

I tried using the quote function but the query still didn't return any rows.

Re: problem importing list and using in proc sql

Astounding — Thu, 29 May 2014 18:57:11 GMT

Dan,

This isn't a solution ... it's just trying to narrow down where the problem is occurring. Try assigning the list with a simple %LET statement:

%let roltypid = 'MD','RMD','MDS','PH','PHC','PHCMP','CMP';

If you still get 0 observations, then there is an issue with SQL interpreting the macro variable. On the other hand if you now get the proper number of observations, the problem lies with the value assigned to &ROLTYPID.

There are similar steps you can take before the PROC SQL, such as (within a macro):

%if &roltypid = 'MD','RMD','MDS','PH','PHC','PHCMP','CMP' %then %put Matched exactly.

Good luck.

Re: problem importing list and using in proc sql

Reeza — Thu, 29 May 2014 19:15:10 GMT

Does the following work for you?

I'm assuming your where clause that's been commented out works correctly?

proc sql;

select quote(compress(name)) into :name_list separated by ","

from sashelp.class

where sex="F";

quit;

%put &name_list;

proc sql;

create table test as

select * from sashelp.class

where name in(&name_list);

quit;

Re: problem importing list and using in proc sql

dan999 — Thu, 29 May 2014 21:26:31 GMT

Yes that works for me. But when I try to put that into my code it still doesn't work. In my code when the data is displayed from the put statement it looks like this

"MD

"RMD

"MDS

etc.

It looks like there is an extra character in there but I've tried trim and strip and nothing gets rid of it.

When I run your code and the list is displayed it looks like this:

"Alice"

"Barbara"

"Carol"

"Jane"

etc.

There is no extra space after the last letter of the name and before the last double quote.

Re: problem importing list and using in proc sql

dan999 — Thu, 29 May 2014 21:33:32 GMT

I just looked at the csv file in a hex editor and there is a return after each code. I created the csv file using excel. I'll try notepad and see if that works.

Re: problem importing list and using in proc sql

dan999 — Thu, 29 May 2014 21:39:37 GMT

I created a txt file in Notepad but when I tried to import it, SAS didn't like the txt. I changed the file extension to csv but when I imported it, there was still an extra something after the code. I've done this before so I don't know why this one is such a problem.

Re: problem importing list and using in proc sql

dan999 — Thu, 29 May 2014 21:53:15 GMT

I got it by creating a txt file and not using commas. The file looked like this:

RDM

MDS

etc.

I used this code:

data roletyp1;

infile "&path./&sysuserid/&custrun./asgnroletypid.txt" MISSOVER;

input role :$7.;

roletypid11=TRIM(LEFT(UPCASE(COMPRESS(COMPRESS(role,"', "),'"'))));

run;

proc sql noprint;

Select compress("'"||roletypid11||"'") into :role3 separated by ","

From roletyp1 ;

Quit;

%put roletypid &role3.;

Thanks for your help.