About Thalo

Thalo · ‎03-27-2014

I figured it out never mind: %macro check; data amort5; set amort2; %do i = 1 %to 400; id&i = lag&i(id); %end; %mend check; %check; %macro still; data amort6; set amort5; if id1 ^= " " then id = id1; %do i = 2 %to 400; else if id&i ^= " " then id = id&i; %end; drop id2-id400 %mend still; %still;

Thalo · ‎03-26-2014

I need to do a lag function for an id observation which I have working manually, but the number of lag I need keeps changing. So can I use an array to help. I use an array to create the variables at first: data amort4; set amort2; array idT (151) $50. id1-id151; do i = 1 to 151; end; run; Second step is Then I am manually writing an lag for each (id) data amort5; set amort4; id1 = lag1(id); id2 = lag2(id); id3 = lag3(id); id4 = lag4(id); /* this goes all the way to 151 with lots of manual typing */ id151 = lag151(id); run; Third step is: Then I basically sort closet to farthest to get the id moved over. I know this is doing things the long way data amort6; *length idf $50. ; set amort5; if id1 ^= "" then idf = id1; else if id2 ^= " " then idf = id2; else if id3 ^= " " then idf = id3; else if id4 ^= " " then idf = id4; /* .... this goes on until id151 */ else if id150 ^= " " then idf = id150; else idf = id151; This gets me an id for every row. Which is alot of manual coding. I would like to be able to use an array or two to set the number of vars created in only one spot.

Thalo · ‎10-15-2013

Thanks arthur, this is really slick. I had to modify a little for cities with two words, but this is exactly what I wanted to do. Now I need to figure out why it works and how it works.

Thalo · ‎10-15-2013

Hello I have an text string that has the address with city, state and zip code. I can extract the state, zip code, city from the end of the string, but I can not figure out how to get the address from the beginning of the string. I can not get the tranwrd function to work using a concatenation of the string from what I have extracted from the string so far. Is there an elegant way to extract the first part of the string that contains the address. here is a sample of what the string looks like: address_string 46 Siwash Creek Rd Tonasket Wa 98855 Po Box 124 Ford Wa 99013 Po Box 292 Ford Wa 99013 Po Box 124 Welpinit Wa 99040 Po Box 47 Wellpinit Wa 99040 Po Box 267 Wellpinit Wa 99040 Po Box 276 Wellpinit Wa 99040 5443 Ford Wellpinit Rd Wellpinit Wa 99040 Po Box 188 Wellpinit Wa 99040 Po Box 416 Wellpinit Wa 99040 5962a Martha Boardman Rd Wellpinit Wa 99040 Po Box 35 Wellpinit Wa 99040 2915 Number One Canyon Rd Wenatchee Wa 98801-2462 785 Skidmore Rd Colville Wa 99114

Thalo · ‎06-06-2013

That works, I've not messed with prxmatch thanks,

Thalo · ‎06-06-2013

Hello I would like to get all the references from a text file in Var2 that match Wyoming or WY. I did right a routine that grabs WY specific items in VAR1 that substr(var1, 5, 7) = "WY"; This will grab most but not all of the files are consistent. So I would like to match things from the text field with the words wyoming, wy and etc. See example. And no Wyoming does not have a consistent place or structure in the text files. Sometimes it is middle and sometimes at the end of Var2. Var1 Var2 Var3 VAR4 Var5 Last Updated A\U\AUD10MD156N.csv 10-Month London Interbank Offered Rate (LIBOR), based on Australian Dollar % D NSA 2013-05-15 G\B\GBP10MD156N.csv 10-Month London Interbank Offered Rate (LIBOR), based on British Pound % D NSA 2013-05-15 C\A\CAD10MD156N.csv 10-Month London Interbank Offered Rate (LIBOR), based on Canadian Dollar % D NSA 2013-05-15 D\K\DKK10MD156N.csv 10-Month London Interbank Offered Rate (LIBOR), based on Danish Krone (DISCONTINUED SERIES) % D NSA 2013-04-10 E\U\EUR10MD156N.csv 10-Month London Interbank Offered Rate (LIBOR), based on Euro % D NSA 2013-05-15 J\P\JPY10MD156N.csv 10-Month London Interbank Offered Rate (LIBOR), based on Japanese Yen % D NSA 2013-05-15 N\Z\NZD10MD156N.csv 10-Month London Interbank Offered Rate (LIBOR), based on New Zealand Dollar (DISCONTINUED SERIES) % D NSA 2013-03-07 S\E\SEK10MD156N.csv 10-Month London Interbank Offered Rate (LIBOR), based on Swedish Krona (DISCONTINUED SERIES) % D NSA 2013-04-10 C\H\CHF10MD156N.csv 10-Month London Interbank Offered Rate (LIBOR), based on Swiss Frank % D NSA 2013-05-15 U\USD10MD156N.csv 10-Month London Interbank Offered Rate (LIBOR), based on U.S. Dollar % D NSA 2013-05-15 D\T\DTP10J22.csv 10-Year 0-1/8% Treasury Inflation-Indexed Note, Due 1/15/2022 % D NSA 2013-05-15 D\T\DTP10J23.csv 10-Year 0-1/8% Treasury Inflation-Indexed Note, Due 1/15/2023 % D NSA 2013-05-15 D\T\DTP10L22.csv 10-Year 0-1/8% Treasury Inflation-Indexed Note, Due 7/15/2022 % D NSA 2013-05-15 D\T\DTP10L21.csv 10-Year 0-5/8% Treasury Inflation-Indexed Note, Due 7/15/2021 % D NSA 2013-05-15 D\T\DTP10L20.csv 10-Year 1-1/4% Treasury Inflation-Indexed Note, Due 7/15/2020 % D NSA 2013-05-15 T\P\TP10L20.csv 10-Year 1-1/4% Treasury Inflation-Indexed Note, Due 7/15/2020 % M NSA 2013-05-01

Thalo · ‎12-01-2012

Tom thanks for your response. Perhaps I am not asking for what I need correctly. I am having no difficulty in getting the data in SAS and creating a data set. I am able to get the column built and filled in only for the rows that have Loan # on them. What I can not figure out is how to get the correct loan number and account name for all the observations below them. I would put something on the system to show you but it does not like my copying to this email box. I did attach a excel file which I see does not show where I am stuck very well. I am going to place a new file called sample.xls and hopefully this will show you better what I am trying to accomplish.. Sorry but I am at home and using a MAC and things are not working correctly. Thanks for your help.

Thalo · ‎12-01-2012

The data is an excel format or a csv format. I imported it into the system as an csv.

Thalo · ‎12-01-2012

Hello I am trying to clean up data from a system generated report. The data has header or unique identification by observations but not a way to identify each observation with an id or account. I want to copy the observation and place it in a column. The sample spreadsheet shows the structure of the data. For my data I want to copy information in the row like: Loan #: 04-0026 Account Name: Chew Dewey Barbeque The id would go in the id column and Chew Dewey Barbeque would go in the account name column. Then the column would be filled with this data until the end of the record. It is easy to make a column that copies into ID and Account name the observation. But I can not figure out a way to copy this data for all the other relevant observations that occur below it, as I can not sort the data yet being there is no unique identification yet. Does anyone know of a way to copy the observations down until the next observation occurs. This observation of a new record will have a header row and a line that will be like the above with new data. Eg Loan #: ID Account Name Actual Name. Here is a shorten example. Please see attachment for full structure. Thanks Loan #: 04-0026 Account Name: Chew Dewey Barbeque Page #: 11 Date Action Amount Disbursed Capitalized Recovery Balance 07/09/2004 Commitment 1000 0 0 0 0 07/09/2004 Advance: Principal 1000 1000 0 0 1000 Loan Totals 1000 0 0 0 Loan #: 04-0037 Account Name: Salmon River Plaza 07/12/2004 Commitment 27500 0 0 0 0 07/12/2004 Advance: Principal 27500 27500 0 0 27500 08/11/2004 Payment: Customized 600.8 0 0 0 27059.62 09/22/2004 Payment: Customized 545.8 0 0 0 26734.81

Online Status	Offline
Date Last Visited	‎09-01-2015 07:11 AM

Re: Create an array of lag functions for150+ variables

Create an array of lag functions for150+ variables

Re: extracting an address from text string

extracting an address from text string

Re: Parse text string for "Wyoming" or "WY" from txt file

Parse text string for "Wyoming" or "WY" from txt file

Re: copying observations without sorting data for data cleaning

Re: copying observations without sorting data for data cleaning

copying observations without sorting data for data cleaning

Re: Create an array of lag functions for150+ variables

Create an array of lag functions for150+ variables

Re: extracting an address from text string

extracting an address from text string

Re: Parse text string for "Wyoming" or "WY" from txt file

Parse text string for "Wyoming" or "WY" from txt file

Re: copying observations without sorting data for data cleaning

Re: copying observations without sorting data for data cleaning

copying observations without sorting data for data cleaning