If you can use a data step to process your dimension table: [pre] data new ; set dimens.table ; compo_key = catx( '-', of keypart1-keypart4 ) ; row +1 ; run ; [/pre] The CATX() function will handle mixed data-types among the variables that it concatenates.[pre] row +1 ;[/pre] will provide a row number variable in the "WORK.NEW" output table. Is that what you need? PeterC ... View more

before your "export statements", try the statement option missing= '.' ; Good Luck PeterC ... View more

there must be other ways to obtain the information you need from a "function-style" macro because between the 🙂 and 😉 of a statement using your "function-style" macro like [pre] 🙂 %maxcol( some.data_set ) 😉 [/pre] your macro cannot generate any semi-colons, and certainly not a step, without interrupting the statement in which %maxcol() is used. Without generating semi-colons, there are ways to obtain for example: * the NOBS of a SAS data set ~ from sashelp.vtable * the maximum value of a variable in a dataset If the macro must use open() fetch() and other functions, they must be called within a %sysfunc() "environment", not as plain base SAS syntax like proc contents data= &syslast out= _data_ noprint; run; A function-style macro must generate only macro language statements, because everything else is the result returned by your macro. You will need to recognise and understand the semi-colons that are part of your macro language statements and those which are generated. The semi-colon of a %let statement is macro language. In the macro statement [pre] %if &A eq &B %then %do ; A %end ;[/pre] no semi colons are generated. However this will generate four semi-colons[pre] %if not %sysfunc( exist( &ds )) %then %do ; data _null_ ; file print ; put "no data in &ds"; run; %end ;[/pre] Hope this helps clarify what a "function-style" macro needs, and why your proposal could not work. Good Luck PeterC Message was edited by: Peter.C ... View more

free download of SAS System Viewer get the version 8 version at http://www.sas.com/apps/demosdownloads/sassysview_PROD_8.2_sysdep.jsp?packageID=000176&jmpflag=N PeterC ... View more

of course the OR logic would provide a substitute for IN However, IN should be working..... if you set system option different from the default NOmInOperator 😉 Try %put abc = %eval( 1 in 1 2 3 ) ; %put def = %eval( 1 in 1,2, 3 ) ; If you want to use a comma separator for the IN list, then set option mInDeliminator Good Luck PeterC ... View more

as it is coming from a base SAS data set, you can vary Oliver's program, just a little to obtain top performance from v.large tables.[pre] DATA last10 ; do _n_ = total_n - 9 to totalN ; SET dataset NOBS=totalN POINT=_n_ ; output ; end ; RUN ; [/pre] Using POINT access ensures you read only the last 10 rows, even if your table has a billion rows. Total_n is acquired at compile time, even before the SET statement is executed. Of course, that information may not be available if the input dataset does not support the "point access" that base SAS tables allows. Examples of that limitation occur with data views and non-SAS tables like tables accessed through a SAS/Access to rdbms. PeterC ... View more

some people recommend the address cleaning services. ~ Perhaps that is, or should be, part of the Data Integration/Data Flux, or the "Q in ETL to the power of Q". Although this is more of a language thing, it might be nice to have a proc that would help with address cleaning! The immediate parsing of the numbers in the address is less of a problem than deciding for what that number is provided, e.g.floor/apartment/building/business-park-block number! OK here is the first number [pre] number1 = input( scan( full_address,1, 'a' ), 32. );[/pre] Just alter that 1 to N to get the N-th number out of the address. I shall have to wait for SAS9.2 to see if the addition of 'a' modifier to SCAN() works the way that suits this challenge. Here is something I've tested in SAS9.1.3, with the examples Cynthia provided[pre]data trial; infile cards; input full_address $char80. ; number1 = input( substr( full_address, 1 , findc( trim(full_address) !!'0-' , ' 1234567890', 'v' ) -1 ) , 32. ); list;cards; 0001050 Elm 00001050 Elm??? or ...[/pre] It is not as attractive as the SAS9.2 solution, but for now, it delivers. ~~ as below [pre]+FSVIEW: WORK.TRIAL (B)--------------------------------------+ | number1 full_address | | | | 1050 0001050 Elm | | 1050 00001050 Elm??? or | | 123 000123 NE 1st St??? Is the field justified| | 123 123 Elm | | 123 0123 Elm | | 123 00123 Elm | | 123 000123 Elm | | 123 0000123 Elm | | 123 00000123 Elm | | 1050 1050 Elm | | 1050 001050 Elm | | 1050 01050 Elm | | 1050 00001050 Elm | | 1111 1111 Twisted Turny Long Avenue, NE | | . | | 150 0000150TH and | | 1050 0001050 Elm if you don't stop it at the rig| | 0 0000NE 1st ST (so you'd stop at the alpha c| | 0 000SE 10th Ave | | 0 000RR #3 | | 0 0000SR 23 | | 0 0000PO Box 61 | | 123 123 Elm might have these variations: | | . | | 123 000000000000000000000000000123 Elm | | 123 000000000000000000000000123 Elm St | | 123 00000000000000000000000123 Elm St. | | 123 00000000000000000000123 Elm Street | | 1050 000000000000000000000000001050 Elm | | 1234 0000001234 NE 1st Street, Extended | | 1111 1111 Twisted Turny Long Avenue, NE | | | +-------------------------------------------------------------+[/pre] missing values for "number1" were created for blank datalines. PeterC ... View more

my preference is to recommend the excel library engine that is part of SAS/Access to PC Files. Then the embedded line feed remains a problem, among others. However it is the cleanest connection I've seen. have you something better? PeterC ... View more

do any of your workbooks contain embedded linefeeds? ... View more

some "people" like to have long strings formatted on more than one line in an excel worksheet cell - not with default line breaks, but breaking at points of their choosing. One way to insert linefeed is to press alt+enter when editing the cell in excel. When that worksheet is exported as a text or CSV file, the cell with that linefeed is delivered with a '0A'x where alt+enter was pressed. This is the embedded linefeed I referred to. That '0A'x causes infile processing to recognise end-of-line. This is before normal input processing allows us to translate the '0A'x into something meaningful, or blank. That '0A'x does not go into the _infile_ buffer. There are few satisfactory ways of cleaning up the data. The most reliable is to get the originator to replace those embedded linefeeds with character(s) or blank. In a SAS program the neatest solution is to treat the file as having no record structure (recfm = U or N) and look for a '0A'x without the preceeding '0D'x (CR). There is a reasonable argument to be made that text files should not contain control characters like linefeed in their data, so these should be removed by excel when creating the CSV file (formulas and macro features and other such bits are removed, so why should that embedded LF not be removed?). SAS Institute seem to listen more than other suppliers. Rather than wait for Microsoft to accept my suggestion, I think it will be easier to suggest to SAS Institute that the CRLF value for the TERMSTR= infile option means that (and that a stand-alone LF is not the TERMSTR value so should not be treatred as indicating end-of-line. Am I being unreasonable ? PeterC ... View more

I think you are placing responsibility in the wrong place for problems that don't really occur. A user's SAS programs execute in their private session, so there is less likelihood of names competing while users program independantly. If they are in programming in a team, I think it is a team responsibility to manage such name contention because each member's programs are expected to co-exist. If not, there is no name contention because the code will not execute in the same SAS session. A SAS program launched by EG will execute in a independent SAS session. Surely you can define macro source library concatenation so that a user's private macros can exist independantly while they also share team and installation macro libraries. These should all support a scheme like OPTION SASAUTOS= ( usermac teamMac siteMac) ; I hope your environment will support such a structure. I think it is more likely to solve your need within our lifetime than SAS chosing to abandon legacy-support. Of course we are all entitled to our opinions ~ mine are just another $0.02 PeterC ... View more

sounds like a minor task for a data step. Just read a line and hold it; identify it's type and read relevant variables; if it is a company heading "RETAIN" those values; if it is a regular set of variables, "OUTPUT" a data row. You may want these data in separate tables. The DATA statement allows more than one output data set to be defined. The OUTPUT statement allows you to nominate the table into which the row should be written. There may even be an example in the manual. Search for "reading hierarchical files". I think that is the terminology for your data structure. Good Luck PeterC ... View more

why would you need to remove a macro? PeterC ... View more

of course, SAS9 brought us the SAS.VFORMAT dictionary of all formats and informats currently available through the libraries and catalogs defined in the FMTSEARCHPATH system option, as well as the built-in formats. I expect it is from this dictionary table that E.G. builds the format-picking lists It is a very useful resource, but needs consideration and the understanding you get (take for granted?) automatically with something like SAS Enterprise Guide PeterC ... View more