SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.

Dear Members,

I have below piece of inputs with comma separated entries with 5 columns. I need to extract only column first (e.g, ABC), four (e.g., 654), and fifth (e.g., 14/04/2023 14:17:14), can someone suggest best way to get these. 

Input:

"ABC","DATA","G:\Data\ces 202302.csv",654,14/04/2023 14:17:14
"ABC","DATA","G:\Data\ces 202302.csv.bak",654,14/04/2023 14:00:41
"XYZ","DATA","G:\Data\ces 202303.csv",5770,14/04/2023 14:17:39
"XYZ","DATA","G:\Data\ces 202303.csv.bak",5770,14/04/2023 09:49:28
"DEF","DATA","G:\Data\CES individuele.xlsx",104637,23/03/2023 14:53:38
"DEF","DATA","G:\Data\CES.xlsx",23370,23/03/2023 14:53:19
"DEF","DATA","G:\Data\12GR.csv",539,08/02/2023 15:12:13

Output:

"ABC",654,14/04/2023 14:17:14
"ABC",654,14/04/2023 14:00:41
"XYZ",5770,14/04/2023 14:17:39
"XYZ",5770,14/04/2023 09:49:28
"DEF",104637,23/03/2023 14:53:38
"DEF",23370,23/03/2023 14:53:19
"DEF",539,08/02/2023 15:12:13

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

I would suggest reading that fifth column as a datetime value.

data want;                                                                                                                              
  infile cards delimiter=',';                                                                                                           
  length dummy $8 first $8 four 8;
  informat fifth anydtdtm30.;       
  format fifth datetime19.; 
  input first dummy dummy four fifth;                                                                                                   
  drop dummy;                                                                                                                           
cards;                                                                                                                                  
"ABC","DATA","G:\Data\ces 202302.csv",654,14/04/2023 14:17:14                                                                           
"ABC","DATA","G:\Data\ces 202302.csv.bak",654,14/04/2023 14:00:41                                                                       
"XYZ","DATA","G:\Data\ces 202303.csv",5770,14/04/2023 14:17:39                                                                          
"XYZ","DATA","G:\Data\ces 202303.csv.bak",5770,14/04/2023 09:49:28                                                                      
"DEF","DATA","G:\Data\CES individuele.xlsx",104637,23/03/2023 14:53:38                                                                  
"DEF","DATA","G:\Data\CES.xlsx",23370,23/03/2023 14:53:19                                                                               
"DEF","DATA","G:\Data\12GR.csv",539,08/02/2023 15:12:13                                                                                 
;run;   

View solution in original post

3 REPLIES 3
LinusH
Tourmaline | Level 20

I would probably import all fields and then use DROP or KEEP statement to get the desired output.

Data never sleeps
s_lassen
Meteorite | Level 14

You can "skip" variables by reading them into a dummy (which you drop):

data want;                                                                                                                              
  infile cards delimiter=',';                                                                                                           
  length dummy $8 first $8 four 8 fifth $30;                                                                                            
  input first dummy dummy four fifth;                                                                                                   
  drop dummy;                                                                                                                           
cards;                                                                                                                                  
"ABC","DATA","G:\Data\ces 202302.csv",654,14/04/2023 14:17:14                                                                           
"ABC","DATA","G:\Data\ces 202302.csv.bak",654,14/04/2023 14:00:41                                                                       
"XYZ","DATA","G:\Data\ces 202303.csv",5770,14/04/2023 14:17:39                                                                          
"XYZ","DATA","G:\Data\ces 202303.csv.bak",5770,14/04/2023 09:49:28                                                                      
"DEF","DATA","G:\Data\CES individuele.xlsx",104637,23/03/2023 14:53:38                                                                  
"DEF","DATA","G:\Data\CES.xlsx",23370,23/03/2023 14:53:19                                                                               
"DEF","DATA","G:\Data\12GR.csv",539,08/02/2023 15:12:13                                                                                 
;run;          
ballardw
Super User

I would suggest reading that fifth column as a datetime value.

data want;                                                                                                                              
  infile cards delimiter=',';                                                                                                           
  length dummy $8 first $8 four 8;
  informat fifth anydtdtm30.;       
  format fifth datetime19.; 
  input first dummy dummy four fifth;                                                                                                   
  drop dummy;                                                                                                                           
cards;                                                                                                                                  
"ABC","DATA","G:\Data\ces 202302.csv",654,14/04/2023 14:17:14                                                                           
"ABC","DATA","G:\Data\ces 202302.csv.bak",654,14/04/2023 14:00:41                                                                       
"XYZ","DATA","G:\Data\ces 202303.csv",5770,14/04/2023 14:17:39                                                                          
"XYZ","DATA","G:\Data\ces 202303.csv.bak",5770,14/04/2023 09:49:28                                                                      
"DEF","DATA","G:\Data\CES individuele.xlsx",104637,23/03/2023 14:53:38                                                                  
"DEF","DATA","G:\Data\CES.xlsx",23370,23/03/2023 14:53:19                                                                               
"DEF","DATA","G:\Data\12GR.csv",539,08/02/2023 15:12:13                                                                                 
;run;   

sas-innovate-white.png

🚨 Early Bird Rate Extended!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9.

 

Lock in the best rate now before the price increases on April 1.

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 734 views
  • 0 likes
  • 4 in conversation