About Scott_Mitchell

Scott_Mitchell · ‎09-25-2013

You can make a macro variable global using the SYPUTX function as below. %MACRO ONE (INPUT); %TWO; %PUT THE VALUE IS &DATE; %MEND; %MACRO TWO; DATA _NULL_; CALL SYMPUTX('DATE','12SEP2008','G'); RUN %MEND; %LET DATE=31DEC2006; %ONE(&DATE); %PUT _USER_;

Scott_Mitchell · ‎09-25-2013

I converted your sample to CSV and imported it successfully using: PROC IMPORT DATAFILE="E:\TEST50.CSV" OUT=WANT DBMS=CSV REPLACE; GETNAMES=YES; RUN;

Scott_Mitchell · ‎09-25-2013

Then why would you send me a sample that is TAB delimited, when your file is CSV??? Please provide the data as a csv.

Scott_Mitchell · ‎09-25-2013

This appears to work. DATA WORK.TEST50_TXT; LENGTH VDSID_HHID $ 16 YEAR 8 'LANDHOLDING GROUP'N $ 6 'DATE OF INTERVIEW'N 8 'CATEGORY OF ITEMS'N $ 8 ITEM_NAME $ 24 'UNIT OF ITEM'N $ 8 QUANTITY 8 'TOTAL VALUE'N 8 ; FORMAT VDSID_HHID $CHAR16. YEAR BEST4. 'LANDHOLDING GROUP'N $CHAR6. 'DATE OF INTERVIEW'N DDMMYY10. 'CATEGORY OF ITEMS'N $CHAR8. ITEM_NAME $CHAR24. 'UNIT OF ITEM'N $CHAR8. QUANTITY BEST4. 'TOTAL VALUE'N BEST4. ; INFORMAT VDSID_HHID $CHAR16. YEAR BEST4. 'LANDHOLDING GROUP'N $CHAR6. 'DATE OF INTERVIEW'N DDMMYY10. 'CATEGORY OF ITEMS'N $CHAR8. ITEM_NAME $CHAR24. 'UNIT OF ITEM'N $CHAR8. QUANTITY BEST4. 'TOTAL VALUE'N BEST4. ; INFILE 'E:\TEST50.TXT' LRECL=85 FIRSTOBS=2 DLM='09'X MISSOVER DSD ; INPUT VDSID_HHID : $CHAR16. YEAR : ?? BEST4. 'LANDHOLDING GROUP'N : $CHAR6. 'DATE OF INTERVIEW'N : ?? DDMMYY10. 'CATEGORY OF ITEMS'N : $CHAR8. ITEM_NAME : $CHAR24. 'UNIT OF ITEM'N : $CHAR8. QUANTITY : ?? COMMA4. 'TOTAL VALUE'N : ?? COMMA4. ; RUN;

Scott_Mitchell · ‎09-25-2013

Is it importing as character? Can you please attach the csv or a subset that you know causes this issue?

Scott_Mitchell · ‎09-25-2013

There are a few ways to do this but the following works nicely. FILENAME FILES PIPE "DIR E:\DRUGS*.TSV /B /O:N"; DATA _NULL_; INFILE FILES TRUNCOVER END=LAST; INPUT FNAME $200.; I+1; CALL SYMPUT("FNAME"||TRIM(LEFT(PUT(I,8.))),TRIM(FNAME)); IF LAST THEN CALL SYMPUT("TOTAL",TRIM(LEFT(PUT(I,8.)))); CALL SYMPUT("FNAMEDATE"||TRIM(LEFT(PUT(I,8.))),SUBSTR(TRIM(FNAME),15,8)); RUN; OPTIONS MPRINT MLOGIC SYMBOLGEN; %MACRO IMPORTS; %DO I=1 %TO &TOTAL.; DATA DRUGS1; INFILE "E:\&&FNAME&I" DELIMITER = "09"X MISSOVER DSD LRECL=32767 FIRSTOBS=2 ; LENGTH DRUG_NAME $ 6 DRUG_ID 8 CONPANY_ID 8 COMPANY_NAME $ 5 ; FORMAT DRUG_NAME $CHAR6. DRUG_ID BEST1. CONPANY_ID BEST1. COMPANY_NAME $CHAR5. ; INFORMAT DRUG_NAME $CHAR6. DRUG_ID BEST1. CONPANY_ID BEST1. COMPANY_NAME $CHAR5. ; INPUT DRUG_NAME : $CHAR6. DRUG_ID : ?? BEST1. CONPANY_ID : ?? BEST1. COMPANY_NAME : $CHAR5. ; ; SOURCE_FILE_NAME="&&FNAME&I"; RUN; PROC APPEND BASE = DRUGS DATA = &SYSLAST.; RUN; %END; %MEND; %IMPORTS; You could also use Call Execute in a similar way.

Scott_Mitchell · ‎09-24-2013

Anil, Does you example data above represent the format of the data in the external file or the data once imported to a SAS dataset? Regards, Scott

Scott_Mitchell · ‎09-24-2013

How about the following? DATA WANT; SET HAVE; LENGTH Y $200 DONE 3; DO I = 1 BY 1 UNTIL (DONE); Y1 = SCAN(X,I); IF LENGTH(Y1) = 3 THEN Y = CATX(' ',Y,Y1); IF Y1 = '' THEN DONE = 1; END; DROP Y1 DONE; RUN;

Scott_Mitchell · ‎09-18-2013

Here is another method I found in my code repository. The previous example output to variables with a prefix of reordered. The below will sort and output to the variables with a X prefix. DATA WANT; SET HAVE; ARRAY A{25} X1-X25; ** SET SORT PARAMETERS ; SEQ = "A" ; * A = ASCENDING, D = DESCENDING ; NODUPKEY = 0 ; * 0 = DUPLICATES ALLOWED, 1 = DUPLICATES NOT ALLOWED ; DCL HASH HH (HASHEXP: 0, ORDERED: SEQ) ; DCL HITER HI ("HH") ; HH.DEFINEKEY ("K","N") ; * N - EXTRA ENUMERATING KEY ; HH.DEFINEDATA ("K") ; * K AUTOMATICALLY ASSUMES ARRAY DATA TYPE ; HH.DEFINEDONE ( ) ; ** LOAD COMPOSITE (K N) KEY ON THE TABLE ; ** IF DUPLICATES TO BE RETAINED, SET 0 <- N ; DO J = LBOUND (A) TO HBOUND (A) ; N = J * ^ NODUPKEY ; K = A {J} ; HH.REPLACE() ; END ; ** USE ITERATOR HI TO RELOAD ARRAY FROM HH TABLE, NOW IN ORDER ; N = LBOUND (A) - 1 ; DO RC = HI.FIRST() BY 0 WHILE ( RC = 0 ) ; N = N + 1 ; A {N} = K ; RC = HI.NEXT() ; END ; Q = N ; ** FILL ARRAY TAIL WITH MISSING VALUES IF DUPLICATES ARE DELETE ; DO N = Q + 1 TO HBOUND (A) ; A {N} = . ; END ; ** CHECK IF ARRAY IS NOW SORTED ; SORTED = 1 ; DO N = LBOUND (A) + 1 TO Q WHILE ( SORTED ) ; IF A {N - 1} > A {N} THEN SORTED = 0 ; END ; PUT SORTED = ; KEEP X:; RUN ; Good Luck

Scott_Mitchell · ‎09-18-2013

Thanks guys, that was perfect.

Scott_Mitchell · ‎09-18-2013

Hi Bernhard, The largest function will identify the nth largest value from a set of variables you specify. DATA WANT; SET HAVE; LARGEST = LARGEST(1,X1,X2,X3,X4,X5,X6,X7,X8,X9,X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,X20,X21,X22,X23,X24,X25); RUN; Which can be consolidated to: DATA WANT; SET HAVE; LARGEST = LARGEST(1,of X1-X25); RUN; You could also use an array to resort the values, that way the value in x5 is always the 5th largest of each observation. DATA TWO; KEEP REORDERED:; SET HAVE; ARRAY X(25); ARRAY REORDERED(25); DO K=1 TO 25; REORDERED(K)=largest(K, OF X1-X25); END; RUN; Regards, Scott

Scott_Mitchell · ‎09-18-2013

Hi Guys, I have a string where the value I wish to extract varies from observation to observation. I have created the following code as an example: DATA STUFF; STRING = '"><ahref"/browse/601/0/3"><imgsrc="/static/img/prev.gif"border="0"alt="Previous"/></a> <ahref="/browse/601/0/3">1</a> 2 <ahref="/browse/601/2/3">3</a> <ahref="/browse/601/3/3">4</a> <ahref="/browse/601/4/3">5</a> <ahref="/browse/601/5/3">6</a> <ahref="/browse/601/6/3">7</a> <ahref="/browse/601/7/3">8</a> <ahref="/browse/601/8/3">9</a> <ahref="/browse/601/9/3">10</a> <ahref="/browse/601/10/3">11</a> <ahref="/browse/601/11/3">12</a> <ahref="/browse/601/12/3">13</a> <ahref="/browse/601/13/3">14</a> <ahref="/browse/601/14/3">15</a> <ahref="/browse/601/15/3">16</a> <ahref="/browse/601/16/3">17</a> <ahref="/browse/601/17/3">18</a> <ahref="/browse/601/18/3">19</a> <ahref="/browse/601/19/3">20</a> <ahref="/browse/601/20/3">21</a> <ahref="/browse/601/21/3">22</a> <ahref="/browse/601/22/3">23</a> <ahref="/browse/601/23/3">24</a> <ahref="/browse/601/24/3">25</a> <ahref="/browse/601/25/3">26</a> <ahref="/browse/601/26/3">27</a> <ahref="/browse/601/27/3">28</a> <ahref="/browse/601/28/3">29</a> <ahref="/browse/601/29/3">30</a> <ahref="/browse/601/2/3"><imgsrc="/static/img/next.gif"border="0"alt="Next"/></a> '; PATTERN = PRXPARSE('#"/browse/\d+\/\d+/\d+"><imgsrc="/static/img/next.gif"border="0"alt="Next"/></a> #'); CALL PRXSUBSTR(PATTERN,STRING,START,LENGTH); SUB = SUBSTR(STRING,START,LENGTH); RUN; The result looks like sub = "/browse/601/2/3"><imgsrc="/static/img/next.gif"border="0"alt="Next"/></a> . However I really want to return only the "/browse/601/2/3" portion. Obviously there are numerous ways to do this using non-prx functions, however I was hoping that there was a PRX method I could employ without creating a separate prx pattern to match this substring as below. DATA STUFF; LENGTH SUB $80 SUB1 $15; STRING = '"><ahref"/browse/601/0/3"><imgsrc="/static/img/prev.gif"border="0"alt="Previous"/></a> <ahref="/browse/601/0/3">1</a> 2 <ahref="/browse/601/2/3">3</a> <ahref="/browse/601/3/3">4</a> <ahref="/browse/601/4/3">5</a> <ahref="/browse/601/5/3">6</a> <ahref="/browse/601/6/3">7</a> <ahref="/browse/601/7/3">8</a> <ahref="/browse/601/8/3">9</a> <ahref="/browse/601/9/3">10</a> <ahref="/browse/601/10/3">11</a> <ahref="/browse/601/11/3">12</a> <ahref="/browse/601/12/3">13</a> <ahref="/browse/601/13/3">14</a> <ahref="/browse/601/14/3">15</a> <ahref="/browse/601/15/3">16</a> <ahref="/browse/601/16/3">17</a> <ahref="/browse/601/17/3">18</a> <ahref="/browse/601/18/3">19</a> <ahref="/browse/601/19/3">20</a> <ahref="/browse/601/20/3">21</a> <ahref="/browse/601/21/3">22</a> <ahref="/browse/601/22/3">23</a> <ahref="/browse/601/23/3">24</a> <ahref="/browse/601/24/3">25</a> <ahref="/browse/601/25/3">26</a> <ahref="/browse/601/26/3">27</a> <ahref="/browse/601/27/3">28</a> <ahref="/browse/601/28/3">29</a> <ahref="/browse/601/29/3">30</a> <ahref="/browse/601/2/3"><imgsrc="/static/img/next.gif"border="0"alt="Next"/></a> '; PATTERN = PRXPARSE('#"/browse/\d+\/\d+/\d+"><imgsrc="/static/img/next.gif"border="0"alt="Next"/></a> #'); PATTERN1 = PRXPARSE('#/browse/\d+\/\d+/\d+#'); CALL PRXSUBSTR(PATTERN,STRING,START,LENGTH); SUB = SUBSTR(STRING,START,LENGTH); CALL PRXSUBSTR(PATTERN1,SUB,START1,LENGTH1); SUB1 = SUBSTR(SUB,START1,LENGTH1); RUN; Thank you very much for your help. Regards, Scott

Scott_Mitchell · ‎09-16-2013

My knowledge is based on reading articles, having not benchmarked the two approaches myself. The attached article from 2007 simply states that temporary arrays are the fastest lookup method, without any data to back it up, but I have read several suggesting the same. http://www.nesug.info/Proceedings/nesug07/bb/bb16.pdf I am only new to hash objects, so have spent the last wekk or so playing around with them. I am fine with simple lookups and the like, but there is so much more to them than just that and the individual methods are doing my head in. I do enjoy a challenge though, so hopefully will have them licked shortly and then can begin benchmarking.

Scott_Mitchell · ‎09-16-2013

Vince, I have read numerous articles that compare the key index method against hashes and the key index method still out performed the hash.

Scott_Mitchell · ‎09-16-2013

Actually I believe that it is the most efficient method of lookup, but is quite memory intensive. Check out the attached for more info. http://www2.sas.com/proceedings/sugi26/p008-26.pdf

Online Status	Offline
Date Last Visited	‎09-28-2023 08:59 AM

DI Studio SQL Merge Transformation User Guide

Re: Excel AMO - Deleting Stored Process Object and Retaining Values us...

Excel AMO - Deleting Stored Process Object and Retaining Values using ...

Re: SAS AMO - Passing Output Parameters To Excel

Re: SAS AMO - Passing Output Parameters To Excel

SAS AMO - Passing Output Parameters To Excel

Re: SAS AMO - Silent Execution of Stored Process

SAS AMO - Silent Execution of Stored Process

Re: PROC FCMP - Output Character Value Truncation

Re: PROC FCMP - Output Character Value Truncation

Re: PROC FCMP - Output Character Value Truncation

Juletip #1 - Finding Missing data

Re: Populating All Missing Variables With the Right Most Non-Missing V...

syntax for a stored procedure issue using proc sql

Re: Upcase Column headings

Re: What is the difference between PROC PRINT and PROC REPORT?

Extracting Substrings Via PRX Functions

Re: Using the IFC and IFN functions

Re: I wanted to list all library under which my dataset is present.

Re: Time stamp format help

Re: global macro

Re:Date format for the Date of Interview variable.

Re:Date format for the Date of Interview variable.

Re:Date format for the Date of Interview variable.

Re:Date format for the Date of Interview variable.

Re: combine text in a folder and save the text files' names

Re:Date format for the Date of Interview variable.

Re: Need to Create y

Re: Equivalent of Excel's "Large" function

Re: Extracting Substrings Via PRX Functions

Re: Equivalent of Excel's "Large" function

Extracting Substrings Via PRX Functions

Re: How to use an array while comparing data sets? #Advanced SAS

Re: How to use an array while comparing data sets? #Advanced SAS

Re: How to use an array while comparing data sets? #Advanced SAS

SAS Community Nordic

SAS Analytics Explorers