Yes it possible, with at least more than 4 different techniques. I will point just one. SORT both datasets by acountnumber, then match merge between the two (DATA MERGE) with subsetting IF for non matching observation. Something like this. data OUT; merge BIG (in=BIG) SMALL (in=SMALL); by ACCOUNTNUMBER; * match by ACCOUNTNUMBER; if not (BIG and SMALL); * if non matching key, then output; run; More info on combining datasets: http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001303248.htm http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a002645476.htm Cheers from Portugal. Daniel Santos @ www.cgd.pt. ... View more

Hello. From my knowledge this is the most efficient way of doing this. You could use the SELECT INTO: feature of PROC SQL to populate a macro var after doing the necessary aggregation. But as you already pointed out, there is no support for MEDIAN(X) summary functions (vertically), just the common datastep MEDIAN function (horizontally). Cheers from Portugal. Daniel Santos @ www.cgd.pt. ... View more

For some validations you may succeed by just using some simple validations functions ANYALPHA ANYDIGIT, ANYPUNCT, ANYSPACE, etc. In this case, it is definitely a task for regular expression matching: data OUT; set IN; EXPR='/^\d{5}([\-]\d{4})?$/'; * reg. expression match pattern for ZIP code; REGX=prxparse(strip(EXPR)); * parse the expression; OK=prxmatch(REGX,ZIP); * try the match for ZIP; put ZIP= OK=; * print result; run; More info here: http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/a002288677.htm http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/a002291852.htm http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/a002296115.htm Also check the following tutorial: http://www.regular-expressions.info/tutorial.html Cheers from Portugal. Daniel Santos @ www.cgd.pt. ... View more

Yes there is, and much more efficient too. Both DICTIONARY tables method, PROC CONTENTS (you may have confused this with PROC DATASETS), or even a DATA STEP with the NOBS options will retrieve immediately the count of obs (EXCEPT if the table is a VIEW or DBMS table) WITHOUT the need to scan the whole data as the SELECT COUNT INTO: technique will need to do. Check online documentation for DICTIONARY tables and the CONTENTS procedure: http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a002299818.htm http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a000085768.htm Cheers from Portugal. Daniel Santos @ www.cgd.pt. ... View more

Hello Peter. I was actually asking HelpHelp, because he/she had already posted the question before, and that was my answer. From my SAS DI Studio experience there is nothing wrong mapping different variable sizes (bigger to smaller, which is the case), IF an expression is specified to convert/truncate the source value. SAS DI Studio uses the same principle, as stated in the error message. Cheers from Portugal. Daniel Santos @ www.cgd.pt. ... View more

UPDATE will do. If you have both tables sorted by account number, then you could also perform a DATA MERGE between the two tables, being the smaller one on the right side of the MERGE statement (to overwrite values of the left side table). Cheers from Portugal. Daniel Santos @ www.cgd.pt. ... View more

Have you tried this? > Yes there is. > > Check the online documentation for the TRUNC > function: > http://support.sas.com/documentation/cdl/en/lrdict/617 > 24/HTML/default/a000245963.htm > > Cheers from Portugal. > > Daniel Santos @ www.cgd.pt. ... View more

True. I've faced this issue sometime with data coming from a z/OS platform to Unix. Luckily, PROC SORT procedure provides the SORTSEQ option to enable you to specify the collating sequence for your sort. You can even build up your own translation table that suits your needs through the PROC TRANTAB procedure. See the SORTSEQ option: http://support.sas.com/documentation/cdl/en/hostwin/61924/HTML/default/chsortfstart.htm And the TRANTAB procedure; http://support.sas.com/documentation/cdl/en/nlsref/61893/HTML/default/a000146227.htm Cheers from Portugal. Daniel Santos @ www.cgd.pt. ... View more

> When mapping you can create an expression using i.e. > trim(), strip(), substr() or another function to > change the length. > > But remember to look into why you wan't to change the > length on your target table. True for alphanumeric variables, which is not the case. Of course you could always convert them to alpha, trim, then back to numeric. But, a little bit confusing to do. Cheers from Portugal. Daniel Santos @ www.cgd.pt. ... View more

Yes there is. Check the online documentation for the TRUNC function: http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/a000245963.htm Cheers from Portugal. Daniel Santos @ www.cgd.pt. ... View more

Actually this is quite wrong. You are trying to populate a variable that is shorter in size (1 byte less) than the source expression, so truncation may occur. The right way to avoid this, would be resize BOTH variables to a desired value. What is the range of values of the student_id var? Cheers from Portugal. Daniel Santos @ www.cgd.pt. ... View more

Hi! First sort the data as you wish. see PROC SORT procedure: http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/sort-overview.htm Then through datastep add the DEMAND variable to a CAL retained variable. see how to RETAIN a value: http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/a000214163.htm Cheers from Portugal. Daniel Santos @ www.cgd.pt. ... View more

Yep, agree with Chris. No need for any semicolon after the macro call. But in my case, sometimes the habit of terminating any line of code with a semicolon takes over this understanding. Indeed, not a big sin. Now, a situation that I found to be particularly confusing to understand for some sas coders is the need of a semicolon after the %if %then macro statement, say something like this: %macro x(TAB,KEEP,VARS); data _null_; set &TAB %if &KEEP %then (keep = &VARS);; run; /* OR */ data _null_; set &TAB %if &KEEP %then (keep = &VARS); end = _EOF; run; %mend x; Cheers from Portugal. Daniel Santos at www.cgd.pt. ... View more

I see you have already found your way, but I just remembered another handy feature of SAS that might help you if you need to pass some large string to a SAS program. SYSGET function will retrieve the value of any operating environment variable. Don't know, might be handy. Check the online documentation: http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/a000245954.htm Cheers from Portugual. Daniel Santos @ www.cgd.pt. ... View more

Try to open the file through the external file functions, for example like this: %let FILENAME=D:\temp\Teste.xls; data _null_; RC=filename('MYFILE',"&FILENAME"); FID=fopen('MYFILE','I'); if not FID then put "File &FILENAME is in use!"; else put "File &FILENAME opened successfully."; RC=fclose(FID); run; FOPEN functions returns a file identifier (non zero integer) if the file was opened successfully. See more on this here: http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/a000209683.htm Cheers from Portugal. Daniel Santos @ www.cgd.pt. ... View more