Need help to get desired output

kumarsandip975 — Sat, 05 Apr 2025 20:11:37 GMT

I have below sample data and need output as mentioned.

Can you please suggest.

Input

XYZ,DATA,G:\Data\XYZ\DATA\Test1.txt,1234,01/01/2025 14:17:14

XYZ,DATA,G:\Data\XYZ\DATA\Test2.txt---bkp,sfsfsf---,12345,01/01/2025 14:17:14

XYZ,DATA,G:\Data\XYZ\DATA\Test3.txt@#ttabd--.txt,123456,01/01/2025 14:17:14

XYZ,DATA,G:\Data\XYZ\DATA\Test4.txt,1234777,01/01/2025 14:17:14

XYZ,DATA,G:\Data\XYZ\DATA\Test5.txt,123479878,01/01/2025 14:17:14

Output

XYZ,DATA,1234

XYZ,DATA,12345

XYZ,DATA,123456

XYZ,DATA,1234777

XYZ,DATA,123479878

Note : 3rd column is path and file name but file name is having different extra character like , @-- etc, so I am getting blank and output is coming correctly.

Re: Need help to get desired output

Tom — Sat, 05 Apr 2025 23:42:06 GMT

By INPUT I assume you mean some TEXT file that you are reading. Since it looks like a delimited text just use the DSD option on the INFILE statement.

But for that to work you need to have a REAL delimited file. And it looks to me like there is one more field in the second line than in the other lines. That is because field values that contain the delimiter (comma in this case) need to be quoted. For the second line to have 5 values like the others it should look like this:

XYZ,DATA,"G:\Data\XYZ\DATA\Test2.txt---bkp,sfsfsf---",12345,01/01/2025 14:17:14

You should find the process that made that file and fix it so that values with delimiters are quoted. For example if you made the file with SAS make sure to use the DSD option of the FILE statement in the data step that wrote the file.

If you are stuck trying to deal with the invalidly formatted file and only one field can possible have unquoted delimiters then you can get it work by using SCAN() to read from the _INFILE_ automatic variable.

To test first let's convert your example into an actual file we can code with.

option parmcards=csv;
filename csv temp;
parcards4;
XYZ,DATA,G:\Data\XYZ\DATA\Test1.txt,1234,01/01/2025 14:17:14
XYZ,DATA,G:\Data\XYZ\DATA\Test2.txt---bkp,sfsfsf---,12345,01/01/2025 14:17:14
XYZ,DATA,G:\Data\XYZ\DATA\Test3.txt@#ttabd--.txt,123456,01/01/2025 14:17:14
XYZ,DATA,G:\Data\XYZ\DATA\Test4.txt,1234777,01/01/2025 14:17:14
XYZ,DATA,G:\Data\XYZ\DATA\Test5.txt,123479878,01/01/2025 14:17:14
;;;;

Now we can use the fileref CSV to point to that example file.

We can read the first two fields using normal INPUT. Then use SCAN() to extract the other field you wanted by scanning from the right.

data want;
  infile csv dsd truncover;
  length var1 $3 var2 $4 var3 $12 ;
  input var1 var2 ;
  var3 = scan(_infile_,-2,',','mq');
run;

Results:

topic Re: Need help to get desired output in SAS Programming

Need help to get desired output

Re: Need help to get desired output