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: 
Kaos
‎12-15-2017
  • 8 Posts
  • 5 Likes Given
  • 1 Solutions
  • 0 Likes Received

Re: Time Interval (in days) across rows

by Fluorite | Level 6 in SAS Programming
‎07-07-2017 11:27 AM
‎07-07-2017 11:27 AM
Thank you for your suggestion. I used a combination of lag and dif functions to get the answer I was looking for.   My dataset looked like this -   Col1    Col2                 abc     2016/05/23 abc     2016/08/13 abc     2016/11/29 abc     2017/02/15 pqr     2016/04/16 pqr     2017/01/27   The code I used is -   Data File2; Set File1; By col1; Lag_col2 = lag(col2); Dif_col2 = dif(col2); If first.col1 then do;   lag_col2 = .;   dif_col2 = .; End; Run;     The final answerset looked like this -   Col1    Col2                  Lag_col2           Dif_col2 abc     2016/05/23        .                         . abc     2016/08/13        2016/05/23        82 abc     2016/11/29        2016/08/13        108 abc     2017/02/15        2016/11/29        78 pqr     2016/04/16         .                        . pqr     2017/01/27         2016/04/16       286   The column Dif_Col2 gives me the difference between the dates for each id on col1.   Thanks again! ... View more

Time Interval (in days) across rows

by Fluorite | Level 6 in SAS Programming
‎06-26-2017 09:43 PM
‎06-26-2017 09:43 PM
Hello Experts!   I need to calculate the time difference across rows for the data below and assign it to a third column.   Data -   Col1    Col2                 abc     2016/05/23 abc     2016/08/13 abc     2016/11/29 abc     2017/02/15   Is there a function I can use to calculate the time interval in days between each record?   Thanks in advance! ... View more

Re: How to manipulate data values in a variable using Macros?

by Fluorite | Level 6 in SAS Programming
‎06-26-2017 09:30 PM
‎06-26-2017 09:30 PM
Hi Tom,   Thanks for your input, you're right, I am trying to improve performance of the code. In the actual code, there are 10 SAS datasets in joins, the final ouput is in SAS and has about 14Million records, and took nearly two and a half hours to execute the code. I am just curious, would creating a macro variable help in anyway?? Something like this -   %let new_col1 = %sysfunc(compress(transtrn(col1, "abcd", "")));   Proc sql; select A.* from Table1   A join Table2   B on &new_column. = B.col2; Quit;   Please let me know your thoughts.   Thanks!   ... View more

Re: How to manipulate data values in a variable using Macros?

by Fluorite | Level 6 in SAS Programming
‎06-26-2017 04:15 PM
‎06-26-2017 04:15 PM
  Thank you for your response. I cannot create any additional table due to space limitations. I am joining on ten different tables so creating temp columns for ten tables with 14 million rows would take up a lot space. ... View more

How to manipulate data values in a variable using Macros?

by Fluorite | Level 6 in SAS Programming
‎06-26-2017 03:33 PM
‎06-26-2017 03:33 PM
Hello -   I need help writing a macro to perform data manipulations on a variable to improve performance of my code.    Simplified Code:   Proc sql; select A.*, B.* from Table1   A left join Table2   B on upcase(transtrn(transtrn(A.col1,"abcde-05",""),"123-A","") = B.col1 Quit;   Problem: Applying functions on join condition has degraded the performance of my code as the tables are big.   Question: Is there a way I can create a macro that takes care of data manipulation externally?    Any other ideas or help will be much appreciated.   Thanks! ... View more

Re: How to Read a single quote as data value from a tab-delimited text...

by Fluorite | Level 6 in SAS Programming
‎05-24-2017 01:09 PM
‎05-24-2017 01:09 PM
@ballardw   I have to read one file every day that may have single quote in some of the columns. This file is usually 2GB in size with about ~20 MM records so can't really open in text editor. However, I used _infile_ to replace the single quote with a different special character and that worked.    Thanks for your input!   ... View more

Re: How to Read a single quote as data value from a tab-delimited text...

by Fluorite | Level 6 in SAS Programming
‎05-24-2017 01:03 PM
‎05-24-2017 01:03 PM
@s_lassen   Thank you for your response. I used your second solution, and that worked with a minor change i.e. I had to include "Input @;" before translating single quote to a different special character.   Thanks again! ... View more

How to Read a single quote as data value from a tab-delimited text fil...

by Fluorite | Level 6 in SAS Programming
‎05-23-2017 05:30 PM
‎05-23-2017 05:30 PM
Hello!   I am trying to read from a tab-delimited text file and running into issues with single quote as a data value. Below is the data set I am working on-   Col1   Col2   Col3   Col4   Col5 Apple            '          '         5 Pear   4        '          '          Grapes  6        2        3       7      The Code I used -   Data test_file;     Infile "&mydir/data_file.txt"  DLM='09'x  DSD  MISSOVER;     Input Col1 :$Char20.              Col2 :$Char20.              Col3 :$Char20.              Col4 :$Char20.              Col5 :$Char20.; Run;   Problem: My code is reading the tab between the consecutive single quotes as the data value, instead of the single quotes as individual data value (for col3 and col4).   I am able to read single quote as a data value when I do not use "DSD" option. However, with missing values in my raw data, I need to use DSD.    Can anyone suggest a solution to resolve this issue?   Any help will be much appreciated!   Thanks!         ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎12-15-2017 05:46 AM
Likes given to
Quick links