@Amir's point #5 is exactly what I referred to in my previous post. Never channel a csv (or other textual data) through Excel. It has a habit of mangling your data into unusable junk.
I wanted to propose the same solution using a dummy variable repeatedly to "jump" over unwanted columns, but Amir beat me to it.
Thanks both,
Here is the list of my variables; I only need pkid, date of birth, ethnic group, sex, Administrative Category,Patient Classification,Admission Method (Hospital Provider Spell),Discharge Destination (Hospital Provider Spell),Discharge Method (Hospital Provider Spell),Source of Admission (Hospital Provider Spell),Start Date (Hospital Provider Spell), Discharge Date (From Hospital Provider Spell), Start Date (Consultant Episode), End Date (Consultant Episode), Diagnosis Scheme In Use (ICD),Diagnosis Primary (ICD), Diagnosis 1st Secondary (ICD),Diagnosis 2nd Secondary (ICD),Diagnosis 3rd Secondary (ICD),Diagnosis 4th Secondary (ICD),Diagnosis 5th Secondary (ICD),Diagnosis 6th Secondary (ICD),Diagnosis 7th Secondary (ICD), Diagnosis Primary (Read),Diagnosis 1st Secondary (Read),Diagnosis 2nd Secondary (Read),Diagnosis 3rd Secondary (Read),Diagnosis 4th Secondary (Read),Diagnosis 5th Secondary (Read),Diagnosis 6th Secondary (Read), Primary Procedure (OPCS), 2nd Procedure (OPCS),3rd Procedure (OPCS),4th Procedure (OPCS), 5th Procedure (OPCS). My question would be, do I have to specify dummy for each variable I don't want in my data set?
apc1_apc2_link,pkid,Date of Birth,Birth Weight,Live or Still Birth,Carer Support Indicator,Legal Status Classification on Admission (Psychiatric Census Only),Ethnic Group,Marital Status (Psychiatric Census Only),Withheld Identity Reason,Sex,Pregnancy Total Previous Pregnancies,Name Format Code,Patient Name,Person Title,Person Given Name,Person Family Name,Person Name Suffix,Person Initials,Address Format Code,Patient Usual Address,Postcode,Organisation Code (Residence Responsibility),PCT of Residence,Organisation Code Type (PCT of Residence),OSV Classification at CDS Activity Date,Overseas Visitor Status Classification 1,Overseas Visitor Status Start Date 1,Overseas Visitor Status End Date 1,Overseas Visitor Status Classification 2,Overseas Visitor Status Start Date 2,Overseas Visitor Status End Date 2,Overseas Visitor Status Classification 3,Overseas Visitor Status Start Date 3,Overseas Visitor Status End Date 3,Overseas Visitor Status Classification 4,Overseas Visitor Status Start Date 4,Overseas Visitor Status End Date 4,Overseas Visitor Status Classification 5,Overseas Visitor Status Start Date 5,Overseas Visitor Status End Date 5,Hospital Provider Spell Number,Administrative Category,Patient Classification,Admission Method (Hospital Provider Spell),Discharge Destination (Hospital Provider Spell),Discharge Method (Hospital Provider Spell),Source of Admission (Hospital Provider Spell),Start Date (Hospital Provider Spell),Start Time (Hospital Provider Spell),Discharge Date (From Hospital Provider Spell),Discharge Time (Hospital Provider Spell),Discharge To Hospital At Home Service Indicator,Episode Number,First Regular Day Night Admission,Last Episode In Spell Indicator,Neonatal Level of Care,Operation Status,Psychiatric Patient Status,Start Date (Consultant Episode),Start Time (Episode),End Date (Consultant Episode),End Time (Episode),Length Of Stay Adjustment (Rehabilitation),Length Of Stay Adjustment (Specialist Palliative Care),Commissioning Serial Number,Provider Reference Number,Commissioner Reference Number,Organisation Code Code of Provider,Organisation Code Type of Provider,Organisation Code Code of Commissioner,Organisation Code Type of Commissioner,Consultant Code,Main Specialty Code,Treatment Function Code,Local Sub Specialty Code,Multi-Professional Or Multidisciplinary Ind Code,Rehabilitation Assessment Team Type,Diagnosis Scheme In Use (ICD),Diagnosis Primary (ICD),Present On Admission Indicator Diag 1,Diagnosis 1st Secondary (ICD),Present On Admission Indicator Diag 2,Diagnosis 2nd Secondary (ICD),Present On Admission Indicator Diag 3,Diagnosis 3rd Secondary (ICD),Present On Admission Indicator Diag 4,Diagnosis 4th Secondary (ICD),Present On Admission Indicator Diag 5,Diagnosis 5th Secondary (ICD),Present On Admission Indicator Diag 6,Diagnosis 6th Secondary (ICD),Present On Admission Indicator Diag 7,Diagnosis 7th Secondary (ICD),Present On Admission Indicator Diag 8,Diagnosis 8th Secondary (ICD),Present On Admission Indicator Diag 9,Diagnosis 9th Secondary (ICD),Present On Admission Indicator Diag 10,Diagnosis 10th Secondary (ICD),Present On Admission Indicator Diag 11,Diagnosis 11th Secondary (ICD),Present On Admission Indicator Diag 12,Diagnosis 12th Secondary (ICD),Present On Admission Indicator Diag 13,Diagnosis 13th Secondary (ICD),Present On Admission Indicator Diag 14,Diagnosis 14th Secondary (ICD),Present On Admission Indicator Diag 15,Diagnosis 15th Secondary (ICD),Present On Admission Indicator Diag 16,Diagnosis 16th Secondary (ICD),Present On Admission Indicator Diag 17,Diagnosis 17th Secondary (ICD),Present On Admission Indicator Diag 18,Diagnosis 18th Secondary (ICD),Present On Admission Indicator Diag 19,Diagnosis 19th Secondary (ICD),Present On Admission Indicator Diag 20,Diagnosis 20th Secondary (ICD),Present On Admission Indicator Diag 21,Diagnosis 21st Secondary (ICD),Present On Admission Indicator Diag 22,Diagnosis 22nd Secondary (ICD),Present On Admission Indicator Diag 23,Diagnosis 23rd Secondary (ICD),Present On Admission Indicator Diag 24,Diagnosis Scheme In Use (Read),Diagnosis Primary (Read),Diagnosis 1st Secondary (Read),Diagnosis 2nd Secondary (Read),Diagnosis 3rd Secondary (Read),Diagnosis 4th Secondary (Read),Diagnosis 5th Secondary (Read),Diagnosis 6th Secondary (Read),Diagnosis 7th Secondary (Read),Diagnosis 8th Secondary (Read),Diagnosis 9th Secondary (Read),Diagnosis 10th Secondary (Read),Diagnosis 11th Secondary (Read),Diagnosis 12th Secondary (Read),Diagnosis 13th Secondary (Read),Diagnosis 14th Secondary (Read),Diagnosis 15th Secondary (Read),Diagnosis 16th Secondary (Read),Diagnosis 17th Secondary (Read),Diagnosis 18th Secondary (Read),Diagnosis 19th Secondary (Read),Diagnosis 20th Secondary (Read),Diagnosis 21st Secondary (Read),Diagnosis 22nd Secondary (Read),Diagnosis 23rd Secondary (Read),Procedure Scheme In Use (OPCS),Primary Procedure (OPCS),Primary Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 1,Professional Registration Issuer Code Opcs 1,Responsible Anaesthetist Code Opcs 1,Responsible Anaesthetist Reg Body Opcs 1,2nd Procedure (OPCS),2nd Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 2,Professional Registration Issuer Code Opcs 2,Responsible Anaesthetist Code Opcs 2,Responsible Anaesthetist Reg Body Opcs 2,3rd Procedure (OPCS),3rd Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 3,Professional Registration Issuer Code Opcs 3,Responsible Anaesthetist Code Opcs 3,Responsible Anaesthetist Reg Body Opcs 3,4th Procedure (OPCS),4th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 4,Professional Registration Issuer Code Opcs 4,Responsible Anaesthetist Code Opcs 4,Responsible Anaesthetist Reg Body Opcs 4,5th Procedure (OPCS),5th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 5,Professional Registration Issuer Code Opcs 5,Responsible Anaesthetist Code Opcs 5,Responsible Anaesthetist Reg Body Opcs 5,6th Procedure (OPCS),6th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 6,Professional Registration Issuer Code Opcs 6,Responsible Anaesthetist Code Opcs 6,Responsible Anaesthetist Reg Body Opcs 6,7th Procedure (OPCS),7th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 7,Professional Registration Issuer Code Opcs 7,Responsible Anaesthetist Code Opcs 7,Responsible Anaesthetist Reg Body Opcs 7,8th Procedure (OPCS),8th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 8,Professional Registration Issuer Code Opcs 8,Responsible Anaesthetist Code Opcs 8,Responsible Anaesthetist Reg Body Opcs 8,9th Procedure (OPCS),9th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 9,Professional Registration Issuer Code Opcs 9,Responsible Anaesthetist Code Opcs 9,Responsible Anaesthetist Reg Body Opcs 9,10th Procedure (OPCS),10th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 10,Professional Registration Issuer Code Opcs 10,Responsible Anaesthetist Code Opcs 10,Responsible Anaesthetist Reg Body Opcs 10,11th Procedure (OPCS),11th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 11,Professional Registration Issuer Code Opcs 11,Responsible Anaesthetist Code Opcs 11,Responsible Anaesthetist Reg Body Opcs 11,12th Procedure (OPCS),12th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 12,Professional Registration Issuer Code Opcs 12,Responsible Anaesthetist Code Opcs 12,Responsible Anaesthetist Reg Body Opcs 12,13th Procedure (OPCS),13th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 13,Professional Registration Issuer Code Opcs 13,Responsible Anaesthetist Code Opcs 13,Responsible Anaesthetist Reg Body Opcs 13,14th Procedure (OPCS),14th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 14,Professional Registration Issuer Code Opcs 14,Responsible Anaesthetist Code Opcs 14,Responsible Anaesthetist Reg Body Opcs 14,15th Procedure (OPCS),15th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 15,Professional Registration Issuer Code Opcs 15,Responsible Anaesthetist Code Opcs 15,Responsible Anaesthetist Reg Body Opcs 15,16th Procedure (OPCS),16th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 16,Professional Registration Issuer Code Opcs 16,Responsible Anaesthetist Code Opcs 16,Responsible Anaesthetist Reg Body Opcs 16,17th Procedure (OPCS),17th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 17,Professional Registration Issuer Code Opcs 17,Responsible Anaesthetist Code Opcs 17,Responsible Anaesthetist Reg Body Opcs 17,18th Procedure (OPCS),18th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 18,Professional Registration Issuer Code Opcs 18,Responsible Anaesthetist Code Opcs 18,Responsible Anaesthetist Reg Body Opcs 18,19th Procedure (OPCS),19th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 19,Professional Registration Issuer Code Opcs 19,Responsible Anaesthetist Code Opcs 19,Responsible Anaesthetist Reg Body Opcs 19,20th Procedure (OPCS),20th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 20,Professional Registration Issuer Code Opcs 20,Responsible Anaesthetist Code Opcs 20,Responsible Anaesthetist Reg Body Opcs 20,21st Procedure (OPCS),21st Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 21,Professional Registration Issuer Code Opcs 21,Responsible Anaesthetist Code Opcs 21,Responsible Anaesthetist Reg Body Opcs 21,22nd Procedure (OPCS),22nd Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 22,Professional Registration Issuer Code Opcs 22,Responsible Anaesthetist Code Opcs 22,Responsible Anaesthetist Reg Body Opcs 22,23rd Procedure (OPCS),23rd Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 23,Professional Registration Issuer Code Opcs 23,Responsible Anaesthetist Code Opcs 23,Responsible Anaesthetist Reg Body Opcs 23,24th Procedure (OPCS),24th Procedure Date (OPCS),Main Operating Healthcare Professional Code Opcs 24,Professional Registration Issuer Code Opcs 24,Responsible Anaesthetist Code Opcs 24,Responsible Anaesthetist Reg Body Opcs 24,Procedure Scheme In Use (READ),Primary Procedure (Read),Primary Procedure Date (Read),2nd Procedure (Read),2nd Procedure Date (Read),3rd Procedure (Read),3rd Procedure Date (Read),4th Procedure (Read),4th Procedure Date (Read),5th Procedure (Read),5th Procedure Date (Read),6th Procedure (Read),6th Procedure Date (Read),7th Procedure (Read),7th Procedure Date (Read),8th Procedure (Read),8th Procedure Date (Read),9th Procedure (Read),9th Procedure Date (Read),10th Procedure (Read),10th Procedure Date (Read),11th Procedure (Read),11th Procedure Date (Read),12th Procedure (Read),12th Procedure Date (Read),13th Procedure (Read),13th Procedure Date (Read),14th Procedure (Read),14th Procedure Date (Read),15th Procedure (Read),15th Procedure Date (Read),16th Procedure (Read),16th Procedure Date (Read),17th Procedure (Read),17th Procedure Date (Read),18th Procedure (Read),18th Procedure Date (Read),19th Procedure (Read),19th Procedure Date (Read),20th Procedure (Read),20th Procedure Date (Read),21st Procedure (Read),21st Procedure Date (Read),22nd Procedure (Read),22nd Procedure Date (Read),23rd Procedure (Read),23rd Procedure Date (Read),24th Procedure (Read),24th Procedure Date (Read),Ward Code at Episode Start Date,Ward Security Level at Episode Start Date,Location Class at Episode Start Date,Site Code (of Treatment) At Episode Start Date,Organisation Code Type (Site Code of Treatment) (At Start of Episode),Intended Clinical Care Intensity(At Start of Episode),Age Group Intended (At Start of Episode),Sex of Patients (At Start of Episode),Ward Day Period Availability,Ward Night Period Availability,Ward Code 1,Ward Security Level 1,Location Class 1,Site Code (of Treatment) 1,Organisation Code Type (Site code of Treatment) 1,Intended Clinical Care Intensity 1,Age Group Intended 1,Sex of Patients 1,Ward Day Period Availability 1,Ward Night Period Availability 1,Start Date 1,Start Time (Ward Stay) 1,End Date 1,End Time (Ward Stay) 1,Ward Code 2,Ward Security Level 2,Location Class 2,Site Code (of Treatment) 2,Organisation Code Type (Site code of Treatment) 2,Intended Clinical Care Intensity 2,Age Group Intended 2,Sex of Patients 2,Ward Day Period Availability 2,Ward Night Period Availability 2,Start Date 2,Start Time (Ward Stay) 2,End Date 2,End Time (Ward Stay) 2,Ward Code 3,Ward Security Level 3,Location Class 3,Site Code (of Treatment) 3,Organisation Code Type (Site code of Treatment) 3,Intended Clinical Care Intensity 3,Age Group Intended 3,Sex of Patients 3,Ward Day Period Availability 3,Ward Night Period Availability 3,Start Date 3,Start Time (Ward Stay) 3,End Date 3,End Time (Ward Stay) 3,Ward Code 4,Ward Security Level 4,Location Class 4,Site Code (of Treatment) 4,Organisation Code Type (Site code of Treatment) 4,Intended Clinical Care Intensity 4,Age Group Intended 4,Sex of Patients 4,Ward Day Period Availability 4,Ward Night Period Availability 4,Start Date 4,Start Time (Ward Stay) 4,End Date 4,End Time (Ward Stay) 4,Ward Code 5,Ward Security Level 5,Location Class 5,Site Code (of Treatment) 5,Organisation Code Type (Site code of Treatment) 5,Intended Clinical Care Intensity 5,Age Group Intended 5,Sex of Patients 5,Ward Day Period Availability 5,Ward Night Period Availability 5,Start Date 5,Start Time (Ward Stay) 5,End Date 5,End Time (Ward Stay) 5,Ward Code 6,Ward Security Level 6,Location Class 6,Site Code (of Treatment) 6,Organisation Code Type (Site code of Treatment) 6,Intended Clinical Care Intensity 6,Age Group Intended 6,Sex of Patients 6,Ward Day Period Availability 6,Ward Night Period Availability 6,Start Date 6,Start Time (Ward Stay) 6,End Date 6,End Time (Ward Stay) 6,Ward Code 7,Ward Security Level 7,Location Class 7,Site Code (of Treatment) 7,Organisation Code Type (Site code of Treatment) 7,Intended Clinical Care Intensity 7,Age Group Intended 7,Sex of Patients 7,Ward Day Period Availability 7,Ward Night Period Availability 7,Start Date 7,Start Time (Ward Stay) 7,End Date 7,End Time (Ward Stay) 7,Ward Code 8,Ward Security Level 8,Location Class 8,Site Code (of Treatment) 8,Organisation Code Type (Site code of Treatment) 8,Intended Clinical Care Intensity 8,Age Group Intended 8,Sex of Patients 8,Ward Day Period Availability 8,Ward Night Period Availability 8,Start Date 8,Start Time (Ward Stay) 8,End Date 8,End Time (Ward Stay) 8,Ward Code 9,Ward Security Level 9,Location Class 9,Site Code (of Treatment) 9,Organisation Code Type (Site code of Treatment) 9,Intended Clinical Care Intensity 9,Age Group Intended 9,Sex of Patients 9,Ward Day Period Availability 9,Ward Night Period Availability 9,Start Date 9,Start Time (Ward Stay) 9,End Date 9,End Time (Ward Stay) 9,Ward Code 10,Ward Security Level 10,Location Class 10,Site Code (of Treatment) 10,Organisation Code Type (Site code of Treatment) 10,Intended Clinical Care Intensity 10,Age Group Intended 10,Sex of Patients 10,Ward Day Period Availability 10,Ward Night Period Availability 10,Start Date 10,Start Time (Ward Stay) 10,End Date 10,End Time (Ward Stay) 10,Ward Code 11,Ward Security Level 11,Location Class 11,Site Code (of Treatment) 11,Organisation Code Type (Site code of Treatment) 11,Intended Clinical Care Intensity 11,Age Group Intended 11,Sex of Patients 11,Ward Day Period Availability 11,Ward Night Period Availability 11,Start Date 11,Start Time (Ward Stay) 11,End Date 11,End Time (Ward Stay) 11,Ward Code 12,Ward Security Level 12,Location Class 12,Site Code (of Treatment) 12,Organisation Code Type (Site code of Treatment) 12,Intended Clinical Care Intensity 12,Age Group Intended 12,Sex of Patients 12,Ward Day Period Availability 12,Ward Night Period Availability 12,Start Date 12,Start Time (Ward Stay) 12,End Date 12,End Time (Ward Stay) 12,Ward Code 13,Ward Security Level 13,Location Class 13,Site Code (of Treatment) 13,Organisation Code Type (Site code of Treatment) 13,Intended Clinical Care Intensity 13,Age Group Intended 13,Sex of Patients 13,Ward Day Period Availability 13,Ward Night Period Availability 13,Start Date 13,Start Time (Ward Stay) 13,End Date 13,End Time (Ward Stay) 13,Ward Code 14,Ward Security Level 14,Location Class 14,Site Code (of Treatment) 14,Organisation Code Type (Site code of Treatment) 14,Intended Clinical Care Intensity 14,Age Group Intended 14,Sex of Patients 14,Ward Day Period Availability 14,Ward Night Period Availability 14,Start Date 14,Start Time (Ward Stay) 14,End Date 14,End Time (Ward Stay) 14,Ward Code 15,Ward Security Level 15,Location Class 15,Site Code (of Treatment) 15,Organisation Code Type (Site code of Treatment) 15,Intended Clinical Care Intensity 15,Age Group Intended 15,Sex of Patients 15,Ward Day Period Availability 15,Ward Night Period Availability 15,Start Date 15,Start Time (Ward Stay) 15,End Date 15,End Time (Ward Stay) 15,Ward Code 16,Ward Security Level 16,Location Class 16,Site Code (of Treatment) 16,Organisation Code Type (Site code of Treatment) 16,Intended Clinical Care Intensity 16,Age Group Intended 16,Sex of Patients 16,Ward Day Period Availability 16,Ward Night Period Availability 16,Start Date 16,Start Time (Ward Stay) 16,End Date 16,End Time (Ward Stay) 16,Ward Code 17,Ward Security Level 17,Location Class 17,Site Code (of Treatment) 17,Organisation Code Type (Site code of Treatment) 17,Intended Clinical Care Intensity 17,Age Group Intended 17,Sex of Patients 17,Ward Day Period Availability 17,Ward Night Period Availability 17,Start Date 17,Start Time (Ward Stay) 17,End Date 17,End Time (Ward Stay) 17,Ward Code 18,Ward Security Level 18,Location Class 18,Site Code (of Treatment) 18,Organisation Code Type (Site code of Treatment) 18,Intended Clinical Care Intensity 18,Age Group Intended 18,Sex of Patients 18,Ward Day Period Availability 18,Ward Night Period Availability 18,Start Date 18,Start Time (Ward Stay) 18,End Date 18,End Time (Ward Stay) 18,Ward Code 19,Ward Security Level 19,Location Class 19,Site Code (of Treatment) 19,Organisation Code Type (Site code of Treatment) 19,Intended Clinical Care Intensity 19,Age Group Intended 19,Sex of Patients 19,Ward Day Period Availability 19,Ward Night Period Availability 19,Start Date 19,Start Time (Ward Stay) 19,End Date 19,End Time (Ward Stay) 19,Ward Code 20,Ward Security Level 20,Location Class 20,Site Code (of Treatment) 20,Organisation Code Type (Site code of Treatment) 20,Intended Clinical Care Intensity 20,Age Group Intended 20,Sex of Patients 20,Ward Day Period Availability 20,Ward Night Period Availability 20,Start Date 20,Start Time (Ward Stay) 20,End Date 20,End Time (Ward Stay) 20,Ward Code 21,Ward Security Level 21,Location Class 21,Site Code (of Treatment) 21,Organisation Code Type (Site code of Treatment) 21,Intended Clinical Care Intensity 21,Age Group Intended 21,Sex of Patients 21,Ward Day Period Availability 21,Ward Night Period Availability 21,Start Date 21,Start Time (Ward Stay) 21,End Date 21,End Time (Ward Stay) 21,Ward Code 22,Ward Security Level 22,Location Class 22,Site Code (of Treatment) 22,Organisation Code Type (Site code of Treatment) 22,Intended Clinical Care Intensity 22,Age Group Intended 22,Sex of Patients 22,Ward Day Period Availability 22,Ward Night Period Availability 22,Start Date 22,Start Time (Ward Stay) 22,End Date 22,End Time (Ward Stay) 22,Ward Code 23,Ward Security Level 23,Location Class 23,Site Code (of Treatment) 23,Organisation Code Type (Site code of Treatment) 23,Intended Clinical Care Intensity 23,Age Group Intended 23,Sex of Patients 23,Ward Day Period Availability 23,Ward Night Period Availability 23,Start Date 23,Start Time (Ward Stay) 23,End Date 23,End Time (Ward Stay) 23,Ward Code 24,Ward Security Level 24,Location Class 24,Site Code (of Treatment) 24,Organisation Code Type (Site code of Treatment) 24,Intended Clinical Care Intensity 24,Age Group Intended 24,Sex of Patients 24,Ward Day Period Availability 24,Ward Night Period Availability 24,Start Date 24,Start Time (Ward Stay) 24,End Date 24,End Time (Ward Stay) 24
Those column headers are going to be useless as variable names. They will make good LABELs to attach to the variables.
What I would do to begin to wrangle such as file is first get the label data out of the first line and into a dataset. To start just make up same variable names, like VAR001, VAR002,... VAR207.
data varlist ;
infile example obs=1 dsd ;
length name $32 label $255 ;
name=cats('var',put(_n_,z3.));
input label @@;
run;
You could then use this dataset to generate code that you could run (or edit by hand and then run) to read the data lines into a dataset.
Start by just defining each variable as character with a length of 100 bytes.
filename program temp;
data _null_;
file program ;
put 'data want;'
/ ' infile example dsd truncover firstobs=2;'
;
do p=1 to nobs;
set varlist point=p nobs=nobs;
put ' attrib ' name 'length=$100 ' label=:$quote. ';' ;
if p=1 then firstvar=name;
if p=nobs then lastvar=name;
end;
put ' input ' firstvar '-- ' lastvar ';'
/ 'run;'
;
stop;
run;
%include program / source2;
Once you look at the actual data (or read the labels and understand what type of data it contains) you can change the LENGTH. For numeric numeric variables (including dates) use LENGTH=8, without the dollar sign. For date, time and datetime variables include INFORMAT= and FORMAT= settings in the ATTRIB statement. There is no need to attach either informat or format to character strings or normal numbers.
There is not much value it attempted to skip/drop variables you don't need BEFORE you have read them. Just add a DROP statement for them to the data step and they will not included in the dataset that the data step creates.
In case it has not been made clear from other posts, the answer to your question:
My question would be, do I have to specify dummy for each variable I don't want in my data set?
is: if they are between variables you want then yes, if they are after the last variable you want then no.
Where there are multiple consecutive variables that are not required between variables you do require then you can, for example, use the technique shown in the code I previously posted, e.g., (3 * dummy) to skip 3 unwanted variables or (12 * dummy) to skip 12 unwanted variables, etc.
You will need to count the columns yourself to tell SAS how many to read past, as it reads left to right under these circumstances.
Kind regards,
Amir.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: