topic Re: Is there a way to concatenate quoted variables? in SAS Programming

Is there a way to concatenate quoted variables?

shikoulitz — Fri, 23 May 2014 15:55:11 GMT

Hi,

Is there a way to concatenate quoted variables?

Basically, I am trying to remove from sas programs all hard coded parms. I am working on z/OS
and trying to feed the parms from a file. For simplicity I am showing the parms on JCL inline below.

I am trying to replace lines like

If cust not in (‘1’,‘3’,‘5’,‘8’,‘P’,‘Q’) then…

…and feed them thru a file.

But I am stuck trying to concatenate the quoted variables.

I tried feeding the parms quoted as well, and using the CAT flavors, and still not able to concatenate them.

Is there a way to do it?

Here is the code I am currently trying. I am presenting only relevant lines.

Thank you.

Miguel

//* SAS PROGRAM

//SYSIN DD DSN=CTOT.MSD.SASLIB(VMINACTH),DISP=SHR

//* INPUT FILES

//CTOINFO DD *

1 001 HbsTVM 092 123

1 002 1358PQ

1 003 TOPL

… … …

1 999 @

.. .. ..

OPTIONS ERRORS=1 ERRORABEND SOURCE2 FULLSTATS SYMBOLGEN MEMRPT

SORTPGM=HOST BLKSIZE(DISK)=HALF DYNALLOC SORTWKDD=SASS;

/*--------------------------------------------------------------*/

/* GET INFO FROM JCL REGARDING THE EXTENT OF THIS PROCESS */

/*--------------------------------------------------------------*/

DATA INFOP;

INFILE CTOINFO;

INPUT @001 recnum 1.

@003 rectype 3.@;

SELECT (RECTYPE);

WHEN (2) DO;

retain ctyp;

INPUT @007 ctyp : $9.;

end;

WHEN (3) DO;

retain sttyp;

INPUT @007 sttyp : $8.;

end;

… … …

/*--------------------------------------------------------------*/

/* FOLLOWING SECTION READ CUSTOMER ID CONTFORM */

/*--------------------------------------------------------------*/

85 DATA CTOPP;

86 RETAIN CLTYPE STTYPE CLPREF;

87 format clt $4. clttype $35. cpr $5.;

88 INFILE LIPPIN PAD OBS=1000;

104 cltype = "";

105 sttype = "";

106 clpref = "";

107 put 'cl type b ' ctyp ' status ' sttyp;

108 * ------------ for cust type key --------------;

109

110 lt = length(trim(ctyp));

111 do i=1 to lt;

112 clt = "'"||trim(substr(ctyp,i,1))||"',";

113 put 'cl type ' clt;

114 if i = lt then

115 cltype = trim(cltype||"'"||clt||"'");

116 else

117 cltype = trim(cltype||"'"||clt||"',");

118 put 'cl typed ' cltype;

119 end;

... ... ...

cl type b 1358PQ status TOPL

cl type '1',

cl typed

cl type '3',

cl typed

cl type '5',

cl typed

cl type '8',

cl typed

cl type 'P',

cl typed

cl type 'Q',

cl typed

Re: Is there a way to concatenate quoted variables?

Reeza — Fri, 23 May 2014 16:31:23 GMT

I don't know enough about what you're doing so this may be a totally incorrect suggestion especially since I see you're using JCL.

I'd read the parameters into a temp data set and create a macro variable using proc sql.

proc sql noprint;

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

from sashelp.class;

quit;

%put &name_list;

Re: Is there a way to concatenate quoted variables?

Amir — Fri, 23 May 2014 16:47:23 GMT

Hi,

Are you looking for some code like the following?

data _null_;

length invalues $30;

input @1 text $char8.;

invalues=quote(substr(text,1,1));

do until(lengthn(text)=1);

text=substr(text,2);

invalues=catx(',',invalues,quote(substr(text,1,1)));

end;

call symput(cats('invalues',_n_),invalues);

*put _n_= invalues=;

datalines;

HbsTVM

1358PQ

TOPL

;

%put invlaues1=&invalues1;

%put invlaues2=&invalues2;

%put invlaues3=&invalues3;

giving:

invlaues1="H","b","s","T","V","M"

invlaues2="1","3","5","8","P","Q"

invlaues3="T","O","P","L"

Regards,

Amir.

Message was edited by: Amir - formatting

Re: Is there a way to concatenate quoted variables?

jwillis — Fri, 23 May 2014 16:51:40 GMT

Dear shkoulitz;

I'm a former jcl jockey. I'm confused by your question which means I probably do not fully understand your question. The single quotes are not being delivered by your JCL. The quotes are being concatenated in by your SAS code in your build of CLT. If you want to remove the single quotes you can use many tools. If you code CLTc = compress(clt,"'") (untested), CLTc should return a value with 1,3,5, 8, P, Q.

111 do i=1 to lt;

112 clt = "'"||trim(substr(ctyp,i,1))||"',";

113 put 'cl type ' clt;

114 if i = lt then

115 cltype = trim(cltype||"'"||clt||"'");

116 else

117 cltype = trim(cltype||"'"||clt||"',");

118 put 'cl typed ' cltype;

119 end;

Re: Is there a way to concatenate quoted variables?

Peter_C — Fri, 23 May 2014 17:03:11 GMT

Use the quote() function to apply quotes and the dequote() to remove them.

When you need single quotes instead of doubles use the translate function.

Avoid quoting troubles by NOT concatenating quotes onto strings

hope your luck improves

peterC

Re: Is there a way to concatenate quoted variables?

shikoulitz — Fri, 23 May 2014 17:45:03 GMT

jwills, Peter.

I guess I was not clear or my question.

I am trying to feed all hard codes thru a file so I can use the programs for multiple use.

To summarize instead of this instruction

If cust not in (‘1’,‘3’,‘5’,‘8’,‘P’,‘Q’) then…

what I would like to do is something like this

if cust not in cltype ..... * where cltype = ('1','3','5','8','P','Q') ;

that is ... feeding the parms thru a file and concatenating them in a variable...

Maybe Peter is right and I should avoid concaternating quotes into strings, and change the code to a select or if anidated.

but will like to give it a try with Amir's and Reeza's suggestion.

Thanks guys

Miguel

Re: Is there a way to concatenate quoted variables?

Peter_C — Fri, 23 May 2014 19:24:36 GMT

There are 2 issues

1 loading parameters (your design so make it easy)

2 applying the parameters

As Reeza suggests, use that value list like

Where cust not in(&name_list)

But it assumes you have a table of those values.

What is the most practical way to load that list of cust values?

e.g.

data cust_exclusions;

Input xcust $ @@ ;

Cards;

1 3 5 8 P Q

;

proc sql noprint;

select xcust format=$quote. Into :cust_x_list separated by ', '

from cust_exclusions

;

use it as you need in code like

if cust not in( &cust_x_list )

The $quote. format does all the quoting you need and the "separated by" should provide the commas - all that without a concatenation in sight.

then you just have to simplify the loading of the parameter values

peterC

P.S.

sorry I forgot to mention earlier that the $quote fornat can be so helpful in loading quoted values into macro variables

Message was edited by: Peter Crawford seemed a good idea so fixing untested code (now tested and it does what you need)

Re: Is there a way to concatenate quoted variables?

shikoulitz — Tue, 27 May 2014 15:18:26 GMT

thanks guys for your suggestions....

I like Reeza's simple SQL suggestion but not able to proceed with it. I guess I didn't understand what to include instead of

"from SASHELP.class" never used it in mainframe before. As explained below trying to execute SQL statement only for the first Data Step iteration. here is what I my code

IF _N_ = 1 THEN DO;

SET INFOP;

TDATE = TODAY();

proc sql noprint;

select quote(trim(substr(ctyp,1,1)))

into :cltye separated by ", "

from sashelp.class;

%put &cltye;

END;

and here is what I got:. Apparently doesn't like it the sql? what I did wrong?

101 IF _N_ = 1 THEN DO;

102 SET INFOP;

103 TDATE = TODAY();

104 proc sql noprint;

117

ERROR 117-185: There was 1 unclosed DO block.

ERROR: SAS ended due to errors.

I even tried inserting the sql in a separate block and this is what I got...

01 IF _N_ = 1 THEN DO;

02 SET INFOP;

03 TDATE = TODAY();

04 END;

05 IF _N_ = 1 THEN

06 proc sql noprint;

____

180

07 select quote(trim(substr(ctyp,1,1)))

_____

RROR 180-322: Statement is not valid or it is used out of proper order.

RROR 22-322: Syntax error, expecting one of the following: (, ;.

08 into :cltye separated by ", "

____

RROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -

IN, LE, LT, MAX, MIN, NE, NG, NL, NOT, NOTIN, OR, ¬, ¬=, |, ||, ~, ~=.

RROR 76-322: Syntax error, statement will be ignored.

--------------------------------------------------------------------------------------------------------------------------------------

Then I also tried Amir's elegant solution... and I am stuck with the apparent symbolic reference x not resolved.

106 invalues=quote(substr(ctyp,1,1));

107 do until(lengthn(ctyp)=1);

108 ctyp=substr(ctyp,2);

109 invalues=catx(',',invalues,quote(substr(ctyp,1,1)));

110 put 'ctyp ' ctyp ' invalues ' invalues;

111 call symput('cltype',invalues);

112 %put &cltype;

WARNING: Apparent symbolic reference CLTYPE not resolved.

&cltype

113 end;

I should come back with Peter's suggestion

So, what should I do?

Thanks guys,

Miguel

Re: Is there a way to concatenate quoted variables?

shikoulitz — Tue, 27 May 2014 15:47:05 GMT

Peter,

thanks for your suggestion, and basically I am trying to do the same. Here is a summary of my program

All parms being feed from a separate file containing several records. So the first one I do is convert to only one record.

That is long to wide conversion. This long observation contains all parms I will be using in my next data step. That is the reason for the SET INFOP in program

here is the code gracefully suggested by Reeza

if _n_ = 1 then do;

set infop;

tdate= today();

proc sql noprint

select quote(trim(substr(ctyp,1,1)))

into :cltype separated by ", "

from infop; * here are the parms I need ;

and what I get is rejecting the PROC sql statement as mentioned before

and even getting a message for a unclosed do block

Re: Is there a way to concatenate quoted variables?

Reeza — Tue, 27 May 2014 16:07:20 GMT

proc sql is a PROC and needs to be its own step, not embedded in a data step, which it looks like you're trying to do.

Re: Is there a way to concatenate quoted variables?

shikoulitz — Tue, 27 May 2014 16:11:29 GMT

you are right Reeza, that's what I was trying to do.

thanks for pointing it out for me.

let me do it prior to the data step where I need it. I hope it creates only one observation so I can use it

Thanks

Re: Is there a way to concatenate quoted variables?

Reeza — Tue, 27 May 2014 16:15:42 GMT

It creates a macro variable and you use that macro variable in the data step.

Re: Is there a way to concatenate quoted variables?

Amir — Tue, 27 May 2014 16:21:29 GMT

Hi,

My understanding was that the CTOINFO data from the JCL was to be used as the in-values.

The data step I coded sets up the macro variables &invalues1/2/3/etc. for use in later data steps (not the same one), e.g:

if var in (&invalues1) then

The reason you're getting the message is because &cltype can only be used after the data step in which it is created, not inside it. Just move your %put &cltype after the data step.

Regards,

Amir.

Re: Is there a way to concatenate quoted variables?

shikoulitz — Tue, 27 May 2014 16:56:26 GMT

Reeza,

not sure what is wrong now

from my original post I got ctyp = 1358pq, and those are the ones I need in quotes and in a variable called CLTYPE.

what I get out of the sql is this

81 proc sql noprint;

82 select quote(trim(substr(ctyp,1,1)))

83 into :cltype separated by ", "

84 from infop;

85 %put &cltype;

SYMBOLGEN: Macro variable CLTYPE resolves to "1"

"1"

Then I changed and tried as you suggested and this is what I got

81 proc sql noprint;

82 select quote(trim(ctyp))

83 into :cltype separated by ", "

84 from infop;

85 %put &cltype;

SYMBOLGEN: Macro variable CLTYPE resolves to "1358PQ"

"1358PQ"

What I did wrong?

Thanks

Re: Is there a way to concatenate quoted variables?

Reeza — Tue, 27 May 2014 16:59:58 GMT

Your data set only has one observation. Proc print infop to see all the values in that data set.

If it has more it would resolve properly. You need to post more code to diagnose the issue.

Re: Is there a way to concatenate quoted variables?

Peter_C — Tue, 27 May 2014 17:19:19 GMT

What has caused your result

"1358PQ"

is the approach of puting all parameters into one row. The "separated by" approach expects the list of values to be in separate rows.

Please could you explain why all parameters must be in one row of a table before they are transferred to a macro var.

If there is no logical alternative (to support "separated by") then we might be able to offer some ways to achieve what we think you need, The sql methods would be a lot simpler but are almost completely ineffective for just one row.

hoping to hear

peterC

Re: Is there a way to concatenate quoted variables?

shikoulitz — Tue, 27 May 2014 18:25:25 GMT

Hi Guys,

for some strange reason I can't select Amir's suggestions as the one that solved my issue!!!

Peter/Reeza,

your sql suggestion is excellent but works (obviously) with multiple records.

I needed this file to contain only one observation because it contains all parameters I need to run the process.

The idea is to read this file only once and retain the parameters to test the variables in each of the new file being read.

Thank you all for your suggestions.

Help me a lot to understand SAS.

You guys are GREAT!!!

Miguel