@Kurt_Bremser wrote: @Patrick wrote: @scwein  That manual deletion can take time, in my experience, especially for large datasets. Dropping/deleting a file (SAS table) on disk takes almost no time and doesn't depend on size. Not entirely true. Either the FAT (Windows) or the block map (UNIX) needs to be updated to mark blocks as available; with larger files this can take noticeable time. While the UNIX block map needs only one bit per block, Windows stores multi-byte block numbers to build the allocation chain, and has to handle considerably more data during deletion. UNIX, OTOH, needs to remove inodes from the inode table. I know and was on purpose not 100% precise. The point I tried to make: If you read/write GB of data then the time it takes to delete a file is in comparison negligible and shouldn't drive your design.   ... View more

@alepage wrote: As I can see from your SAS code, we cannot read the file directly, we need to import it to read it.   Depending on your SAS version you can use FILENAME Statement: S3 Access Method To read the file "directly" refer to: Example 1: Reading a File by Using the S3 Access Method ... View more

The IF EXIST clause doesn't exist in the SAS SQL flavor which is why you're getting an error.  The following syntax works for deleting SAS tables that may or may not exist. proc datasets lib=work nolist nowarn; delete mytable; quit; ... View more

@KRHE It appears you're interfacing with SQL Server. The issue you encounter is on the DB side with DB SQL. SAS has nothing to do with it. You need to investigate the issue on the DB side ideally using a DB client like MS SQL Server Management Studio or the like.   The Alphabetic List of Variables and Attributes you show us is to what SAS maps the DB table columns to but these are not the actual DB column types. You need to retrieve this information directly on the DB. Using SAS code like below could work. proc sql; connect to oledb as NBP_data (provider=SQLNCLI11 properties=( "Data Source" = "xxxx" "Initial Catalog" = "xxx" "Integrated Security" = "SSPI" "Persist Security" = "False") ; select * from connection to NBP_data ( SELECT ORDINAL_POSITION , COLUMN_NAME , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH , IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_catalog='DDD' and table_schema='DD' and TABLE_NAME = 'PV_APPLICANT' ; ); disconnect from NBP_data; quit;     From what SAS maps the columns to it looks like the DB columns are both of type Datetime. But then your syntax should work... Below executed via MS SQL Server Management Studio. I suggest for debugging purposes just run your where clause against the one column/table where things appear to return an error. If using SAS you could start with something like below - but ideally you're doing this without SAS using a DB client and eventually with some support from a DBA at your site. proc sql outobs=5; connect to oledb as NBP_data (....); select * from connection to NBP_data ( select RowTimestamp from [DDD].[DD].[PV_APPLICANT] where RowTimestamp > '2024-04-10' ; ); disconnect from NBP_data; quit;       ... View more

@scwein  That manual deletion can take time, in my experience, especially for large datasets. Dropping/deleting a file (SAS table) on disk takes almost no time and doesn't depend on size. ... View more

If SAS throws such a warning then things might work but it's not guaranteed. I wouldn't go for such code and certainly never use it productively.   If your data is already sorted in the right way then using a data step merge will perform better. A data step merge will only return a different number of rows compared to a SQL join in case of a many:many relationship. A SQL join will likely sort the data again and as a result during execution consume additional disk space for utility files created by the sorts.   If you just want to add empty columns to an existing table then by all means use SQL alter table - or just a data step with an attrib statement that creates a same named table with the additional columns. If you want to actually add data from another table then what's "best" depends. It can be a SQL, a data step merge, a data step hash lookup, a format, .... It also depends on how you define "best": Best performance, code as simple and easy to maintain as possible, .... ... View more

@Vivy1  To better understand the problem and desired outcome which rows from below sample data would you want to select? And why (selection criteria)? data example; input dt1:date9. dt2:date9.; m1 = intck('month',dt1,dt2); m2 = intck('month',dt1,dt2,'C'); y1 = intck('year',dt1,dt2); y2 = intck('year',dt1,dt2,'C'); format dt1 dt2 date9.; datalines; 29OCT2019 18Nov2020 29OCT2019 28OCT2020 29OCT2019 29OCT2020 29OCT2019 30OCT2020 29OCT2019 01NOV2020 28FEB2019 27FEB2020 28FEB2019 28FEB2020 28FEB2019 29FEB2020 28FEB2019 01MAR2020 29FEB2020 27FEB2021 29FEB2020 28FEB2021 29FEB2020 01MAR2020 28FEB2020 27FEB2021 28FEB2020 28FEB2021 28FEB2020 01MAR2020 ; run; proc print data=example; run;     ... View more

You need to share your code and ideally SAS log with us. Also tell us which database you're interfacing with.   From the looks of it: The use of the cast() function implies that you're using explicit pass-through SQL where SAS sends all the code as-is to the DB for execution. You need to use DB syntax only in explicit pass-through SQL so '04OCT2023:05:12:20.380'dt will certainly not work. In many databases conversion of a string to a timestamp would use syntax like to_timestamp('<date string>'<, ...>). Below sample code from the Oracle documentation: TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')   With at least some databases the date string must be enclosed in single quotes and double quotes are not allowed. On the other hand if you want to pass the date string via a SAS macro variable (which gets resolved prior to SAS sending the code to the DB) then you need to use double quotes. One way to get around this is the use of the SAS provided %tslit() macro that will first resolve the macro variable before adding single quotes. TO_TIMESTAMP (%tslit(&dttm), 'DD-Mon-RR HH24:MI:SS.FF')   A a clue is that when writing conditions for another varchar in the same source i had to convert it to varchar even though it already was a var char (as is the datevar). SAS did simply not read it until I used 'Where cast(y.var as varchar(38)) is not null Given you're using the cast() function above must also be part of explicit pass-through SQL  syntax fully executed on the database side. As such it's something you also need to investigate on the database side. SAS has nothing to do with it.   And last but not least and given your sample using a datetime string with decimals cast(x.RowTimestamp as date) = '04OCT2023:05:12:20.380'  In case you loaded the data first from SAS to the database including variable RowTimestamp then you would also have to deal with numeric precision. Whenever you transfer floating point numbers with decimals from one platform to another (in general, not only with SAS) you can get small differences in the last bits of the numbers. This is due to differences in the data representation on different platforms (there are many articles and discussions out there around this topic - for example here).  If you upload data to a database (or download from SAS) and you then want in the target system to compare this data to some fixed value with decimals then you need to either send the data as string and convert to floating point on the target system (cast) OR you need to round() the number to some non-significant position. Let's say you upload a numerical SAS variable with max 6 significant decimals then round the variable on the target side to the 7th or lower decimal.     ... View more

@Gayatrikunchay   Regular expressions are for pattern matching but it appears you are after exact matches. You could create and use a format for such a case as demonstrated in below code. /* list of desired icd codes */ data want_codes; input icd_code:$9.; datalines; F060 F061 F062 F0630 F0631 F0632 F0633 ; /* prepare data to create format from the desired code list */ data fmt_source; retain fmtname '$ICD_MATCH' type 'C' default 1; set want_codes(rename=(icd_code=start)) end=last; label=1; output; if last then do; label='0'; hlo='O'; output; end; run; /* create format $ICD_MATCH */ proc format cntlin=fmt_source; run; /* use format to determine if icd code is in the desired list */ data demo; input icd_code:$9.; selection_flg=put(icd_code,$icd_match.); datalines; F0630 F0631 F999 F0731 ; proc print data=demo; run; Should you need to find such matching codes in a free text field then you could use a regular expression to extract the terms that could be an icd code and then use above approach to determine if the term matches to any of the codes in your desired list.   ... View more

@shawnchen0321 Read through the answers already provided. The code for converting a special missing .C to a date in the future has already been shared here. ... View more

@davidsmarch1  As @Tom rightfully flagged: Don't use RETURN but DELETE to always ensure the desired result.  Using RETURN will only work if you also add statement OUTPUT before the run statement. ... View more

Below should work as long as your source strings don't contain more than 4 characters. A more generic solution would require that you first analyse your source strings and then create as many target variables as you need for the longest source string. data have; input a_1 $ a_2 $ a_3 $ a_4 $; datalines; 1000 1111 1010 1110 0101 1100 1001 1111 1100 0100 0001 1001 ; run; data want; set have; array src_vars{4} a_1 a_2 a_3 a_4; array trg_vars{4,4} $1 a_11-a_14 a_21-a_24 a_31-a_34 a_41-a_44; do i=1 to dim(src_vars); do k=1 to length(src_vars[i]); trg_vars[i,k]=substr(src_vars[i],k,1); end; end; run; proc print data=want; run;   ... View more

@davidsmarch1 wrote: This currently works and may address some of your suggestions:   You want to read multiple .csv with a single data step where you need to skip (not read) the header line of each of these .csv.  infile 'M:\FPST-VR Pilot Testing\TestLogs Data Collection\*.csv' delimiter = ',' dsd truncover FILENAME=FNAME; ..... if fname ne lag(fname).... FNAME contains the name of the source file that currently gets processed. If the name from the previous iteration of the data step is different - lag(fname) - then you are currently reading the first line of a new .csv which is the header line that you want to skip. That's the case where IF FNAME NE LAG(FNAME) becomes true. Each input statement moves the cursor to the next line of source text file unless you use the @argument. That's fully documented for the INPUT Statement .  You need input @;   to read the next line to populate FNAME with the current name so you can compare it to the name of the previous line. But you need to keep the pointer on the current line so you can actually read this line into SAS variables in case it's not the header line.    If it is the header line then you issue another input statement so the pointer moves to the next line where you've got data. if fname ne lag(fname) then input;   ...but: Above logic will fail should you have a source .csv that only contains a header line without any data. To circumvent such an issue you could instead code as below: input @; if fname ne lag(fname) then RETURN; input Participant_ID best32. ......   I also strongly suggest to use INFORMATS when reading source strings into SAS variables. Informats instruct SAS how to map a source string into a SAS variable.  attrib .... dominant_hand length=$10 INFORMAT=$10. ...and so on.... ...or alternatively use the informat directly within the input statement. input ..... dominant_hand :$10. .... ...and if there are a lot of variables and you're lazy then use the EG/Studio import wizard or Proc Import for one .csv and then copy/paste the generated data step code (with Proc Import from the SAS log) and amend it to what will work for all .csv's.  ... View more