DATA Step, Macro, Functions and more

problem importing list and using in proc sql

Reply
Contributor
Posts: 37

problem importing list and using in proc sql

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.

Super User
Posts: 10,500

Re: problem importing list and using in proc sql

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?


Contributor
Posts: 37

Re: problem importing list and using in proc sql

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

Super User
Posts: 17,829

Re: problem importing list and using in proc sql

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;

Contributor
Posts: 37

Re: problem importing list and using in proc sql

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

Super User
Posts: 5,082

Re: problem importing list and using in proc sql

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.

Contributor
Posts: 37

Re: problem importing list and using in proc sql

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

Super User
Posts: 17,829

Re: problem importing list and using in proc sql

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;

Contributor
Posts: 37

Re: problem importing list and using in proc sql

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.


Contributor
Posts: 37

Re: problem importing list and using in proc sql

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.

Contributor
Posts: 37

Re: problem importing list and using in proc sql

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.

Contributor
Posts: 37

Re: problem importing list and using in proc sql

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

MD

RDM

MDS

PH

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.

Ask a Question
Discussion stats
  • 11 replies
  • 512 views
  • 0 likes
  • 4 in conversation