cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
SasDewd
‎10-29-2019
  • 19 Posts
  • 2 Likes Given
  • 0 Solutions
  • 2 Likes Received

Prior period value listed along with the current period value - LAG qu...

by Obsidian | Level 7 in SAS Programming
‎10-29-2019 10:48 AM
‎10-29-2019 10:48 AM
Hello,   I have a data set that includes Period (20190930, 20190831, 20190731,....), AccountNumber, CurrentBalance, and some other columns. Let's say the data set goes back 2 years or back to Period 20180131. What I want to do is I want to add a column to each row in the data set, called PriorPeriodCurrentBalance, and populate it with, well, the current balance amount from the prior month. I'm pretty sure this can be done with the LAG function and I found some examples but I think I may need to sort my data set first and I can't find examples that do just that. Anyone has any ideas?   Thanks! ... View more

Re: Update columns in one table with values from another table - both ...

by Obsidian | Level 7 in SAS Programming
‎06-13-2019 04:27 PM
‎06-13-2019 04:27 PM
Thank you @ballardw !! Hmm...both Steves have ID = 3  and all the other fields are the same except the Salary field. Perhaps this was not a great example and I was just trying to explain that all I'm really trying to do is take Steve's Salary value from Table2 and assign it to Steve's Salary value in Table1. There cannot be another Steve Gordon with different ID or anything like that.   If this was SQL Server he TSQL code below would have taken care of what I need and it would have finished in seconds:   UPDATE T1 SET    T1.Salary= T2.Salary FROM   Table1 T1 INNER JOIN Table2 T2 ON T1.ID= T2.ID   So I'm just shocked that such a simple thing cannot be achieved in SAS without having to write lines of code and/or running it for hours. Oh well, something new to learn I guess 🙂  ... View more

Re: Update columns in one table with values from another table - both ...

by Obsidian | Level 7 in SAS Programming
‎06-13-2019 03:55 PM
‎06-13-2019 03:55 PM
Thank you @Reeza ... I'll look into what you suggested. My understanding is tha COALESCE returns first nonmissing value but in my case the values may not be missing, they're just not correct and need to be updated.   Anyway, thanks for your help! ... View more

Re: Update columns in one table with values from another table - both ...

by Obsidian | Level 7 in SAS Programming
‎06-13-2019 03:48 PM
‎06-13-2019 03:48 PM
This is the only thing I tried as I assumed it would be a very simple update statement.   Most of the columns I do not want to update just a few of them (about 20 out of 200). So, let's say there are 2 tables:   Table1 ID                    First             Last                    Salary 1                      John           Hopkins               $3,000 2                      Jim              Stevens               $2,000 3                      Steve           Gordon               $1,000   Table2 ID                    First             Last                    Salary 3                     Steve          Gordon               $3,000   All I want to do is update Table1 and make sure that Steve's salary is $3,000, which is the value from Table2. Thanks!   ... View more

Re: Update columns in one table with values from another table - both ...

by Obsidian | Level 7 in SAS Programming
‎06-13-2019 03:38 PM
‎06-13-2019 03:38 PM
Yes, both of the tables are in same library. ... View more

Update columns in one table with values from another table - both tabl...

by Obsidian | Level 7 in SAS Programming
‎06-13-2019 02:46 PM
‎06-13-2019 02:46 PM
Hello,   I have what I thought was a very simple and basic task to do. I have 2 tables, T1 and T2, both of them with exact same table structure. I'm just trying to update a few columns in T1 with values from T2 based on the common key column - ID. Here's my code that appears to be working but that has been running for more than 2 hrs and it still hasn't finished.   PROC SQL; UPDATE WORK.TableToBeUpdated AS T1 SET Column1 = (SELECT Column1 FROM WORK.Newest AS T2 WHERE T1.ID = T2.ID) , Column2 = (SELECT Column2 FROM WORK.Newest AS T2 WHERE T1.ID = T2.ID) WHERE T1.ID IN (SELECT ID FROM WORK.Newest) ; QUIT;   Both of the tables have ~200 columns and ~1mil records. Since I'm used to working in SQL Server, I know this kind of task would take me about 20 secs to both write the command and run it but here either I'm using the wrong approach to solve this or SAS is executing this kind of code completely different. Can you offer any ideas?   Thanks, ... View more

Re: Cleaning data within DATA/INPUT step to remove new line feed and p...

by Obsidian | Level 7 in SAS Programming
‎05-22-2019 03:58 PM
‎05-22-2019 03:58 PM
@ballardw  I just created a small csv file with only 5-6 columns (comment field included) in it and 2 records, both of which are giving me trouble. I was going to send it to you but before I did I tested it running through the slimmed-down code (removed all the other columns) and added TERMSTR=CLRF option....and it worked fine! So, if I add the TERMSTR option to the original code and run it through all the records and all the columns I get no records returned but when I run it against the newly created sample file with only a couple of rows and few columns it works just fine. I get no errors, no warnings but for some reason it just doesn't return any records when I use TERMSTR=CLRF... ... View more

Re: Cleaning data within DATA/INPUT step to remove new line feed and p...

by Obsidian | Level 7 in SAS Programming
‎05-22-2019 02:23 PM
‎05-22-2019 02:23 PM
Thank you @ballardw ! I'll need to do some work and clean the file before I upload it here as most of it is our customers' data.  Let me see if I can put something together.   Thank you @Tom! The CSV file is generated by a separate entity and I have no control over it. It would have been much easier to just get rid of all these characters and new lines before creating the file but, again, it is just dropped in a folder every morning and all I do is pick it up from there and run it through the DATA step I posted above. It could include every possible character as it comes from a comment section.   Thanks again for your answers! ... View more

Re: Cleaning data within DATA/INPUT step to remove new line feed and p...

by Obsidian | Level 7 in SAS Programming
‎05-22-2019 01:01 PM
‎05-22-2019 01:01 PM
Thank you for your reply @ballardw  I tried adding TERMSTR=CRLF but that resulted in no records returned at all    So I have  INFILE "&path/&&File&i" DELIMITER = ',' DSD LRECL=32767 FIRSTOBS=2;   and I changed it to  INFILE "&path/&&File&i" DELIMITER = ',' DSD TERMSTR=CRLF LRECL=32767 FIRSTOBS=2;   but, like I said, I get back an empty data set.   I also forgot to mention, and I think it's important, that some comments have multiple commas (I believe having one comma is OK since there is the DSD option, but having another one would throw it off?). So, I think it's both multiple commas and new line feeds (which there could also be multiple of) that's causing the issue.   Here's a sample of my code (just the variable that's causing the issue)   DATA WORK.DAILY_DATA_FEED; LENGTH Comment $ 275 ... FORMAT Comment $CHAR275. ... INFORMAT Comment $CHAR275. ....   INFILE "&path/&&File&i" DELIMITER = ',' DSD TERMSTR=CRLF LRECL=32767 FIRSTOBS=2;   INPUT Comment : $CHAR275.   One of the comments that is causing an issue is:   '$10,034 paid to customer, $12000 max Thank you. John M.'   As you can see there is a new line feed after max.   Thanks! ... View more

Cleaning data within DATA/INPUT step to remove new line feed and poten...

by Obsidian | Level 7 in SAS Programming
‎05-22-2019 12:30 PM
‎05-22-2019 12:30 PM
Hello,   I have a data step with LENGTH/FORMAT/INFORMAT/INPUT components that imports a CSV file. It's a big piece of code as it has close to 200 fields and yes, I copied it from the import wizard. I've been using this for a while and it's been working fine.   However, I just needed to add another variable to import, Comment, and now I'm having all kinds of issues. The comment variable could and does have all kinds of characters in it and the ones that I think are causing me a headache are new line characters and commas. So what happens when I run my code only part of the comment will be displayed in the Comment field and then all the following fields will get messed up (for example, Comment is supposed to say '$10,000 was paid to the customer' and the CommentDate field that follows should say 3/5/2019 - instead the Comment field is showing $10 and the CommentDate field is showing .).   My question is, is there a way to clean the data coming in somewhere in the INPUT section. For example, where I have: INPUT Comment   : $CHAR275.   I could have something like Comment = TRNWRD(Comment, ".", "")  :$CHAR275.  and/or something similar to remove new line characters. Btw, I tried this and it's not working.   Any ideas?   Thanks! ... View more

Re: Data set's last. variable not working as intended?

by Obsidian | Level 7 in SAS Programming
‎03-21-2019 05:06 PM
‎03-21-2019 05:06 PM
I forgot to mention...especially since the code I had was removing duplicates as I was hoping it would...just not all of them ... View more

Re: Data set's last. variable not working as intended?

by Obsidian | Level 7 in SAS Programming
‎03-21-2019 05:04 PM
‎03-21-2019 05:04 PM
Hmm..OK...I will try that. But why? If I have 2 records: Order_Id         Update_date 100                  1/1/2019 100                  2/1/2019   So I want to keep the one from 2/1/2019 why would I say if  last.order_id and not  last.update_date??   Just trying to understand. Second part makes perfect sense..I will change that.   Thank you for your answer @Astounding !! ... View more

Data set's last. variable not working as intended?

by Obsidian | Level 7 in SAS Programming
‎03-21-2019 04:56 PM
‎03-21-2019 04:56 PM
Hello,   I have a data set full of orders.  The data set has 150+ columns and when I update it every day some duplicates will be introduced. I need to remove those duplicates (based on order_id) and keep the record with the most recent update_date. The following code works most of the time but I just noticed there are duplicates that the code for some reason is not removing and it does not make any sense. Here is my code:   proc sort data=Orders out=Orders; by order_id update_date; run; data Orders; set Orders; by order_id update_date; if last.update_date; run;   What am I missing here and why is this working for some orders but not the others. I've been staring at one particular order for the past 2hrs and it's the same exact order_id and the update_date is clearly different yet the code returns both records!? ... View more

Re: Importing multiple CSV files with different structures results in ...

by Obsidian | Level 7 in SAS Programming
‎01-16-2019 10:46 AM
2 Likes
‎01-16-2019 10:46 AM
2 Likes
@Reeza Thank you! That makes sense so I will definitely look into doing it that way if that would save me the trouble down the road.  One thing I'm concern with is even if I figure it out where all my testing completes successfully I have no way of knowing what kind of data will show up in tomorrow's file and if something unexpected will throw everything off. So, if reading all the values as character would save me from any surprises than that's definitely worth looking into. Thank you! ... View more

Re: Importing multiple CSV files with different structures results in ...

by Obsidian | Level 7 in SAS Programming
‎01-16-2019 10:38 AM
‎01-16-2019 10:38 AM
@Tom Thank you Tom, I will look into the approach you suggested. Unfortunately, I have no control over how the input files are created from MongoDB so I just need to figure it out and make sure it works. Thanks again! ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎10-29-2019 02:51 PM
Likes given to
View All
Likes from
View All
Quick links