I've been using SAS for only 2 years and my next task is rather a complicated one. Summary of the task: I need to export two columns from multiple excel files (xlsx; ~120 files) into one dataset. Each excel file consititute a record or a case. When imported, I would like for the columns from each record to be a row so that in the new dataset, I'll have each record on a row. I understand that for the latter step, I'll need to transpose. Steps taken: I know how to import one entire excel file into a SAS dataset. I've looked at articles but they only discussed importing select columns or rows from one excel file -- NOT from multiple files. What I think needs to be done: Write a code to reference the folder where the excel files are stored. Write a code to extract the two columns of interest from the first excel file and store into a new dataset. Then reiterate this code to continue extracting from the next files until it gets to the last file. Make sure to flag those files that have been imported. The new dataset will now contain the following columns: ID-1, Code-1, ID-2, Code-2, ID-3, Code-3, ... Therefore, next step is to transpose. What I'm avoiding (marked with *): Extract both columns of interest from each excel file into a dataset. This will create ~120 datasets*. Transpose each of the new datasets. Merge all new datasets. Delete the ~120 datasets. Any coding tips will be helpful, and necessary tweaks to my thinking processes will be appreciated. I'm currently using SAS Studio at home, the free version, which makes things a little bit more challenging. Thanks!
... View more