http://support.sas.com/documentation/cdl/en/mcrolref/62978/HTML/default/viewer.htm#n189qvy83pmkt6n1bq2mmwtyb4oe.htm   Scroll down to the tip. ... View more

I second @ChrisNZ 's recommendation of %superq, which I often use if I don't know the structure of the macro variable input (such as a generic macro that may be called by end users).    But, per my other post, I recommend you don't put commas in your macro variables at all, esp. those that may serve as parameters to other macros or macro functions.   P.S.:  Your problem is that that commas in your macro variable are passed as syntax to the %substr() macro function, which then thinks you're passing in too many parameters to that function. ... View more

My own approach to this is, I don't include "syntax" with "data".  In other words, don't include commas, quotation marks, etc, etc in with your macro variables.  Especially commas - you're only going to confuse the macro tokenizer without convoluted quoting that you don't have to do.   Instead, "inject" the syntax into your code at "run time".   For this I use a couple macros:   https://github.com/scottbass/SAS/blob/master/Macro/seplist.sas (originally written by Richard Devenezia) https://github.com/scottbass/SAS/blob/master/Macro/squote.sas (originally written by "Tom") (useful for RDBMS explicit passthrough that requires single quoted literals)   See the macro header for use cases and original author attribution.   So, instead of:   %let list = "foo","bar","blah"; data foo; set bar; where var in (&list); run; I use:   %let list = foo bar blah; data foo; set bar; where var in (%seplist(&list,nest=qq)); run; Hope this helps... ... View more

@JacquesR :   There are a bunch of questions you haven't addressed.  Off the top of my head:   * Do all the workbooks have similar structure? * Are the worksheet names valid SAS dataset names? * Are the worksheet names repeated across workbooks?  If so, how do you handle duplicate names, so datasets don't get overwritten? * Otherwise, what output dataset naming pattern do you want? * Do all the worksheets have valid SAS column names in row 1? * If not, how do you intend to name the resultant SAS columns (likely named F1, F2, etc) * Will your worksheet structure ever change?  For example, will AU workbooks ever have more (or less) than 5 worksheets?   I'm not saying don't use macro - the below approach does use macro - but IMO your macro code is convoluted, hard to follow, and hard to maintain.  Although I do admit my excel2sas macro is complex.  At least I've tried to debug it thoroughly - YMMV.   I'm on SAS 9.3 so I couldn't use the XLSX engine.  But the attached %excel2sas macro did work in my environment.  If you're on SAS 9.4, try changing the libname _XCEL_ to use the XLSX engine.   I threw together a quick test, copying a few Excel workbooks into a directory.  This code imported every worksheet from every workbook in the directory, naming the SAS dataset based on the worksheet name.   Perhaps you can run with this, after gaining an understanding of the macros.   * get list of Excel files ; %dirlist( dir=\\UNC\path\to\files\Excel ,filter=ext='xlsx' ); * macro to run for every record in dirlist ; %macro code; * need to strip leading and trailing spaces ; %let fullname=%sysfunc(strip(&fullname)); %excel2sas(file=&fullname,getnames=yes); %mend; * run %excel2sas for every fullname ; %loop_control(control=dirlist);   From glancing at your code, I'd say you need to fiddle with the dirlist output to derive the correct worksheet names, then feed the correct name into %excel2sas.  Try to derive all your metadata in the datastep, not via macro.   Another option is, if the worksheet name can be "translated" by %excel2sas into a valid SAS dataset name, then post-process the import via additional code (proc datasets rename dataset) in the %code macro, after the call to %excel2sas.   My advice is to get %excel2sas to 1) work in your environment, and 2) test it with a small subset of your workbooks, and 3) see if post-processing will work, 4) otherwise, CAREFULLY modify the macro.   Hope this helps... ... View more

@hvempati wrote: I have a data with numbers like this -   4259311 4262311 18730311 3000001311 3000004311 3000011311 3000013311 3000019311   I get these numbers by joining two rows and have a variable name uni_num I write a macro to loop over all the numbers and create temp table to manipulate each set. But while running the code it does well for all numbers which have length less than 8 but does not detect the numbers such as 3000001311. proc sql; create table temp_1&i as select * from work.input_for where uni_num = &var. ; quit;   where my &var is the number above.   I was trying to change the length of number format but with no luck. Can anyone help me with this?   Thank you.     Your question is unclear.     Forcing me to guess, I'd say you have a dataset containing:   4259311 4262311 18730311 3000001311 3000004311 3000011311 3000013311 3000019311   And another dataset containing records where uni_num = one of those values.   And you're trying to create separate datasets filtered on those numbers.   Questions:   1) Why?  Are you familiar with BY group processing? 2) I suspect creating views rather than tables would give better performance unless your data volume is small.   There are numerous ways to split your data, but first can you say why you need to?   And perhaps make your question clearer?   Oh, and as far as making the length of num longer...is it really num or char?  If num, and you're converting to char (say into a macro variable), use a format, such as 32. or best32. to override the default format SAS uses to convert a number to character.   Of course, this is me again guessing exactly what you're doing.  Regardless, hope this helps... ... View more

@monona wrote: OMG!!!! I LOVE YOU. IT WORKS!!!   Congratulations @SASKiwi !!! 😄 ... View more

There are many ways to do this...here is but one.    You also don't say what you want if there are duplicate data within a session_id, for example session_id=2 and time_sum in (11,99).  In my code, the first value wins.   data have; input session_id :8. device_name :$10. time_sum :8.; datalines; 1 . . 1 desktop . 1 . 12 1 . . 2 . 11 2 . . 2 mobile . 3 desktop 10 3 . 10 ; run; data want; if 0 then set have; %let _hashnum_=0; %hash_define(data=have,keys=session_id,vars=device_name,where=device_name is not missing); %hash_define(data=have,keys=session_id,vars=time_sum,where=time_sum is not missing); set have; %hash_lookup; drop _:; run; Download %hash_define and %hash_lookup (and any supporting macros, such as %parmv) from here:   https://github.com/scottbass/SAS/tree/master/Macro ... View more

@monona wrote: Thanks for your reply! I got error messages though. ERROR: CLI error trying to establish connection: [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ''. : [Microsoft][SQL Server Native Client 11.0]Invalid connection string attribute ERROR: Error in the LIBNAME statement.   Does your SQL Server support trusted connections, i.e. your already authenticated network identity (when you logged in) if sufficient to grant access to SQL Server?   Do you use SQL Server Management Console, or some other tool, to connect to SQL Server outside of SAS?   Before posting more questions, you may want to invest some time here: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001355231.htm   If you haven't yet read this, you should... ... View more

Further to @SASKiwi 's comment, I have written a macro to assist me at my client since I work with SQL Server all the time.   Perhaps you may find this useful, or at least can use it as a starting point.  Obviously you may need to edit it for your site specific requirements.   https://github.com/scottbass/SAS/blob/master/Macro/libname_sqlsvr.sas ... View more