@Jarakata,
There may be a more elegant way to do this, but here's a working solution:
1. First, code and run the Proc Import as I suggested in my first reply.
2. Then, copy the generated data step into an editor window. You'll want to create two copies of the Data step.
3. With the first copy of the Data step, you'll need to pull out the informats and the Input section and place them inside a Macro definition as shown below. Notice that each variable ends with an "&i" in the below example. The Macro will execute a %DO loop three times so as to read the first three lines of your data to get the variable names. I deleted the format statements since they are not necessary here. I also increased any small definitions in the informats to $32 so as not to truncate any titles. The Macro ends with a series of Call Symputx commands to create macro variables that are a concatenation of the first three lines for each column.
There are some requirements here:
a) There can be no embedded blanks in the variable names
b) There can only be numbers, underscores, and letters in the variable names and the first character cannot be a number (I'm using v7 names).
c) The variable names can only have a maximum of 32 characters when all three lines are assembled. If you have more than 32 characters, you'll probably have to truncate to 32 as I did with the second variable in the below example (see the SYMPUTX command).
%MACRO Generate_Input_Statements;
%LOCAL i;
%DO i = 1 %TO 3;
informat CPT_Code&i $32. ;
informat OPSI_Description&i $32. ;
informat Short_Desc&i $35. ;
informat Long_Desc&i $48. ;
informat Sub_Class&i $32. ;
informat dw_updt_dt&i $32. ;
informat Sheet&i $32. ;
input
CPT_Code&i $
OPSI_Description&i $
Short_Desc&i $
Long_Desc&i $
Sub_Class&i $
dw_updt_dt&i $
Sheet&i $
;
%END;
CALL SYMPUTX('CPT_Code', CATS(CPT_Code1, '_', CPT_Code2, '_', CPT_Code3), 'G');
CALL SYMPUTX('OPSI_Description', SUBSTR(CATS(OPSI_Description1, '_', OPSI_Description2, '_', OPSI_Description3), 1, 32), 'G');
CALL SYMPUTX('Short_Desc', CATS(Short_Desc1, '_', Short_Desc2, '_', Short_Desc3), 'G');
CALL SYMPUTX('Long_Desc', CATS(Long_Desc1, '_', Long_Desc2, '_', Long_Desc3), 'G');
CALL SYMPUTX('Sub_Class', CATS(Sub_Class1, '_', Sub_Class2, '_', Sub_Class3), 'G');
CALL SYMPUTX('dw_updt_dt', CATS(dw_updt_dt1, '_', dw_updt_dt2, '_', dw_updt_dt3), 'G');
CALL SYMPUTX('Sheet', CATS(Sheet1, '_', Sheet2, '_', Sheet3), 'G');
%MEND Generate_Input_Statements;
4. Them call the macro with a little Data step like so:
data _NULL_;
infile 'I:\commercial\development\CSI_Suspects\pgm\Research\Test_Only.csv'
delimiter = ','
lrecl=32767
firstobs=1
MISSOVER
DSD
;
%Generate_Input_Statements;
STOP;
RUN;
Notice that firstobs must be 1 in order to capture all three of the first three lines. I'm also specifying Data _NULL_ since I'm just creating macro variables to hold the variable names from the first three rows.
5. Then use the second copy I asked you to get from the SAS log (see step 2, above) and modify all the variable names to be macro variables by putting an ampersand in front of each of the variable names. The macro variable names will be translated by SAS into the variable names created by the first data step.
/**********************************************************************
* PRODUCT: SAS
* VERSION: 9.4
* CREATOR: External File Interface
* DATE: 09SEP20
* DESC: Generated SAS Datastep Code
* TEMPLATE SOURCE: (None Specified.)
***********************************************************************/
data WORK.MY_TEST_DATA ;
infile 'I:\commercial\development\CSI_Suspects\pgm\Research\Test_Only.csv'
delimiter = ','
lrecl=32767
firstobs=2
MISSOVER
DSD
;
informat &CPT_Code $5. ;
informat &OPSI_Description $11. ;
informat &Short_Desc $35. ;
informat &Long_Desc $48. ;
informat &Sub_Class $5. ;
informat &dw_updt_dt $10. ;
informat &Sheet $9. ;
format &CPT_Code $5. ;
format &OPSI_Description $11. ;
format &Short_Desc $35. ;
format &Long_Desc $48. ;
format &Sub_Class $5. ;
format &dw_updt_dt $10. ;
format &Sheet $9. ;
input
&CPT_Code $
&OPSI_Description $
&Short_Desc $
&Long_Desc $
&Sub_Class $
&dw_updt_dt $
&Sheet $
;
RUN;
Now, when we run, the variable names in the output SAS data set are a concatenation of the first three lines for each column. In the example below, I used some nonsensical, repetitious variable names, but, even though they look funny in this example, these are the correct data names based on the first three columns.
Jim
... View more
