thanks . i ended up not going forward with this, but this works. ... View more

Thanks. I am doing something that may require looping through the values. That frequency was just an example, I realize that it's not a good example. ... View more

I ended up using proc import to generate the SAS recognized formats and informats command, and compared across all raw files for each variable. For character variables, I chose $max. For variables that can be both numeric and character, I went back to look at the data to determine what is the correct format to take on. I wrote this into a macro and executed it on all files and stacked them. Certainly not the most efficient way of doing things, but seems to have worked out OK. Thank you so much. ... View more

I only changed the file path of the logs below. The "sheet1" in "excel1.sheet1(&excelDef)" does not have to the real sheet/tab name in the excel file, does it? 5447 %let Exceldef=dbsastype=(arrivalDt=date name=char(50) n=numeric); 5448 5449 libname excel1 excel 'c:\001.xlsx'; NOTE: Libref EXCEL1 was successfully assigned as follows: Engine: EXCEL Physical Name: c:\001.xlsx 5450 libname excel2 excel 'c:\002..xlsx'; NOTE: Libref EXCEL2 was successfully assigned as follows: Engine: EXCEL Physical Name: c:\002.xlsx 5451 5452 data want; 5453 set 5454 excel1.sheet1(&excelDef) SYMBOLGEN: Macro variable EXCELDEF resolves to dbsastype=(arrivalDt=date name=char(50) n=numeric) NOTE: Line generated by the macro variable "EXCELDEF". 1 dbsastype=(arrivalDt=date name=char(50) n=numeric) --------- - 108 108 ERROR 108-63: Invalid syntax for option DBSASTYPE. 5455 excel2.sheet2(&excelDef) SYMBOLGEN: Macro variable EXCELDEF resolves to dbsastype=(arrivalDt=date name=char(50) n=numeric) NOTE: Line generated by the macro variable "EXCELDEF". 1 dbsastype=(arrivalDt=date name=char(50) n=numeric) --------- - 108 108 ERROR 108-63: Invalid syntax for option DBSASTYPE. 5456 ; 5457 run; NOTE: Compression was disabled for data set WORK.WANT because compression overhead would increase the size of the data set. NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 0 observations and 0 variables. WARNING: Data set WORK.WANT was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds ... View more

that's right. i have converted an xlsx file into csv and imported it. i can copy the informat/format/input chunk of the log file and make changes as necessary, which is convenient. but i still have 30 files to import, each with over 100 fields. is there an easier way to determine the correct format for each variable, besides looking at what values a variable contains across all 30 files, and humanly decide what format is optimal to use, so they are consistent for later stacking? ... View more

Thank you. I got this error message: 1 dbsastype=(arrivalDt=date name=char(50) n=numeric) --------- - 108 108 ERROR 108-63: Invalid syntax for option DBSASTYPE. ... View more

For some reason, when I execute proc import, the log does not show the detailed background code as if running a data step. How do I get those lines to show so I can copy and modify and put them into a data step code of my own, and use that on all files? The log I got was: NOTE: The import data set has 354 observations and 115 variables. NOTE: Compressing data set WORK.TRY decreased size by 33.33 percent. Compressed is 4 pages; un-compressed would require 6 pages. NOTE: WORK.TRY data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.27 seconds cpu time 0.23 seconds ... View more

By using a data step to import, I assume you meaning spelling out (in)format specifications for every variable in the dataset, is that correct? I was looking to see if it's possible to avoid doing that on over 100 variables, but if that's the most reliable way to go, then I will do it. Thanks. ... View more

thanks. that in hindsight should have been obvious.. ... View more

Oh and also, what if I want to record all actions in their sequence and not eliminate duplicates? for something like this: transaction action_sequence 1 buy/sell/buy 2 buy/loan/sell ... View more

thank you. that works! ... View more