About Ody

Ody · ‎07-25-2023

This is exactly what i was looking for! I guess I had the right pieces/idea, even have a few tries using arrays but couldn't get it to work. I was struggling (for more time than i care to admit) on how to execute it. Thank you and to everyone else that offered some insight!

Ody · ‎07-25-2023

I do not

Ody · ‎07-25-2023

Hello, I'm trying to parse a column called LINE that is delimited by spaces(' '). Example records: somedata1 somedata2 somedata1 somedata2 somedata3 somedata1 somedate1 somedate2 somedate3 somedate4 The number of delimiters is unknown. To handle this I'm taking the max count of spaces: proc sql noprint; select max(countc(LINE,' ')) + 1 into: nums from testdata; quit; I would like to create additional columns with a prefix of col_ for the max number of delimiters found in the LINE column. So far I have the following code but it's not quite working. data test; set mydata; %macro cols; %do i = 1 %to &nums; col_&i = scan(LINE, &nums, ' '); %end; %mend cols; %cols; run; I would expect the Test dataset to contain 4 additional columns called Col_1, Col_2, Col_3, and Col_4; each containing the delimited values from LINE. Put another way: Col_1 will contain the values equal to somedata1 Col_2 will contain the values equal to somedata2 Col_3 will contain the values equal to somedata3 Col_4 will contain the values equal to somedata4 The results are getting close to the requirements but I'm missing something; its not quite right. I havent coded in SAS in a few years beyond simple CRUD stuff and am quite rusty. Appreciate any help with this, thanks!

Ody · ‎05-04-2020

Thanks for the example!

Ody · ‎05-04-2020

The data source is financial related so it's relatively large. The query pulls data on a per period basis where a period is defined as the start of the month to the end of the month. The data, being financial in nature, needs to be pulled in this manner for analysis. The current process is absurd. Someone is running this query over and over again, changing the start and end date. Then they copying the data over to excel. When I heard of this I new there was an easier way to loop through the query and export the data. I've done similar things in the past with export data sets but the date range is giving me a headache. Maybe I'm over thinking it... I thought I'd get some quick ideas here but instead i'm getting a lot more questions than answers.

Ody · ‎05-04-2020

Thanks for thinking through that though that possible issue but I'm comfortable transforming the data into the appropriate format, I'm just having trouble writing a macro to loop through the months of my date range. I'm really just looking for an example I can use as a frame work to build on.

Ody · ‎05-04-2020

Unfortunately I'm unable to keep things in one data set due to the nature of the data and analysis being performed. I also don't really have much control over the context of the code. I was asked to automate an existing process involving a more complex version of the query in my example.

Ody · ‎05-04-2020

Hi all, I've searched though some of the previous forum posts but couldn't find something relevant to my particular need. I have a query that looks something like this: proc sql; create table month as select * from some_table where date between start_date and end_date; quit; My goal is to create a macro that will increment the start/end date for each month in a given range. For example, I want to create a data set for each month in the range of May 1 2017 to September 1 2019, where start_date and end_date are the beginning and end of each month in that range. I've seen some examples using intx() but I'm having trouble applying that to this scenario. Appreciate any pointers. Thanks in advance.

Ody · ‎05-10-2018

Thanks for the feedback. Unfortunately I need explicit definitions for my my string search. I've tried find/index, and their variations, to no avail. I had some success with PRXMatch but kept running into issues with the metacharacters. I'll give this a go tomorrow, thanks again. edit: Thanks for the help. Solution verified.

Ody · ‎05-10-2018

So, after some more research today I think that PRXMATCH is the way to go however I'm not sure how to account for metacharacters in my URL strings. For example, when I try to match on this URL i get an error: prxmatch('/home.synchrony.com/summer/') I have tried changing this to prxmatch('/home\.synchrony\.com\/summer/') to count the period and forward slash as regular characters but it's not working well for me. I'm not sure what I'm doing wrong. Thoughts?

Ody · ‎05-10-2018

Thanks for the feedback and potential solutions. Ultimately, I need to rethink my approach to this problem. The URLs I'm trying to match against often contain multiple values (keywords) from my lookup list so I need to rethink how those lookup values get prioritized, otherwise I'm still gonna end up with multiple matches for one URL.

Ody · ‎05-08-2018

Results look like this.

Ody · ‎05-08-2018

Hello folks, I have a large table of URL info (and associated fields) I would like to categorize using a partial match to info found in the URL. Instead of writing a case statement to account for this, what would be the best method? I've come across a few cases where people are using index in the where clause to match but that's not working 100% for me. Here is some example data. data test; infile datalines dsd truncover; input match:$200. field1:$200. field2:$200. ; datalines; 'test', 'val1', 'val2' 'testing', 'val3', 'val4' 'tested', 'val5', 'val6' ;;; data test1; infile datalines dsd truncover; input url:$200. ; datalines; 'www.facebook.com/test/fsdfne' 'www.facebook.com/testing/deeheheksjbd' 'www.facebook.com/tested/5wnf4wljn/4rwef' ; If I try some code like what's below Im getting a cartesian product. I only want to match on the exact characters from 'test.match'. How can I accomplish this? As always, appreciate any help! proc sql; create table URL_Match as select a.url, b.match, b.field1, b.field2 from test1 a, test b where index(upper(a.url),strip(upper(b.match))) > 0; quit;

Ody · ‎02-16-2018

Good afternoon all, I'm looking for some insight into the best way to flatten the following data. My goal is to join this data to another table where bin is the primary key. Currently, when I join on bin I'm getting repetitive results because of the one to many relationship. data test; infile datalines dsd truncover; input bin:$200. field1:$200. field2:$200. field3:$200. value1:$200. value2:$200. ; datalines; '123456' ,'GHG' ,'GED' ,'US ON 1' ,'560000' ,'1,000,000' '123456' ,'GHG' ,'GED' ,'US ON 2' ,'560001' ,'1,000,001' '123456' ,'GHG' ,'GED' ,'US ON 3' ,'560002' ,'1,000,002' '123456' ,'GHG' ,'GED' ,'US ON 4' ,'560003' ,'1,000,003' '123456' ,'GHG' ,'TRS' ,'US ON 1' ,'560004' ,'1,000,004' '123456' ,'GHG' ,'TRS' ,'US ON 2' ,'560005' ,'1,000,005' '123456' ,'GHG' ,'TRS' ,'US ON 3' ,'560006' ,'1,000,006' ;;;;;; My assumption is that I need to break these data into separate data sets that look something like this. Is this the right solution or is there another method? data test1; infile datalines dsd truncover; input bin:$200. field1:$200. field2:$200. field3:$200. value1:$200. value2:$200. ; datalines; '123456' ,'GHG' ,'GED' ,'US ON 1' ,'560000' ,'1,000,000' ; data test2; infile datalines dsd truncover; input bin:$200. field1:$200. field2:$200. field3:$200. value1:$200. value2:$200. ; datalines; '123456' ,'GHG' ,'GED' ,'US ON 2' ,'560001' ,'1,000,001' ; data test3; infile datalines dsd truncover; input bin:$200. field1:$200. field2:$200. field3:$200. value1:$200. value2:$200. ; datalines; '123456' ,'GHG' ,'GED' ,'US ON 3' ,'560002' ,'1,000,002' ; data test4; infile datalines dsd truncover; input bin:$200. field1:$200. field2:$200. field3:$200. value1:$200. value2:$200. ; datalines; '123456' ,'GHG' ,'GED' ,'US ON 4' ,'560003' ,'1,000,003' ; data test5; infile datalines dsd truncover; input bin:$200. field1:$200. field2:$200. field3:$200. value1:$200. value2:$200. ; datalines; '123456' ,'GHG' ,'TRS' ,'US ON 1' ,'560004' ,'1,000,004' ; data test6; infile datalines dsd truncover; input bin:$200. field1:$200. field2:$200. field3:$200. value1:$200. value2:$200. ; datalines; '123456' ,'GHG' ,'TRS' ,'US ON 2' ,'560005' ,'1,000,005' ; data test7; infile datalines dsd truncover; input bin:$200. field1:$200. field2:$200. field3:$200. value1:$200. value2:$200. ; datalines; '123456' ,'GHG' ,'TRS' ,'US ON 3' ,'560006' ,'1,000,006' ;

Ody · ‎02-15-2018

You're correct. I forget a few single quotes when supplying my test data. I've changed that and reran the code using both comma24. and comma32. as a format. The results are what I would expect, which is good. This seems to have solved my problem, thanks. Not really sure why when I tried that format earlier I was having issues. Any way, thanks.

Online Status	Offline
Date Last Visited	‎07-26-2023 01:43 AM

Re: dynamic columns

Re: dynamic columns

dynamic columns

Re: Macro to increment months

Re: Macro to increment months

Re: Macro to increment months

Re: Macro to increment months

Macro to increment months

Re: Joining/Matching on URL

Re: Joining/Matching on URL

Re: Character to Numeric conversion issues

Re: Character to Numeric conversion issues

Re: Substring Question

Re: PRXMatch question

Re: dynamic columns

Re: dynamic columns

dynamic columns

Re: Macro to increment months

Re: Macro to increment months

Re: Macro to increment months

Re: Macro to increment months

Macro to increment months

Re: Joining/Matching on URL

Re: Joining/Matching on URL

Re: Joining/Matching on URL

Re: Joining/Matching on URL

Joining/Matching on URL

Flatten Data

Re: Character to Numeric conversion issues