Retaining the variable structure

[manikanta9500]

We have developed a SAS program which import XLSX file which contains 100 variable . This program will execute daily and XLSX file will change the variables and data frequently .

Example : Consider we have 10 variable in yesterdays refresh and today we have only 9 variable in excel  . But we want all 10 variable in SAS dataset when we refresh today . where 9 variable will have data and missing 1 variable will  be part of SAS dataset with blank value .

Kindly request you provide your input how we can archive above scenario. 

Thanks in advance   

[PaigeMiller · Accepted solution]

If you append the SAS data set from today's import to the SAS data set from yesterday's import, you will get a resulting data set with all 10 variables, and missings in the 10th variable from today's import. If you only want the data from today's import, but the variables from both day's imports, you can try this, where I have assumed yesterday's import is named YESTERDAY and ... well, you get the idea.

data want;
     set yesterday(obs=0) today;
run;

[ballardw]

---

@manikanta9500 wrote:

We have developed a SAS program which import XLSX file which contains 100 variable . This program will execute daily and XLSX file will change the variables and data frequently .

Example : Consider we have 10 variable in yesterdays refresh and today we have only 9 variable in excel  . But we want all 10 variable in SAS dataset when we refresh today . where 9 variable will have data and missing 1 variable will  be part of SAS dataset with blank value .

Kindly request you provide your input how we can archive above scenario. 

Thanks in advance   

---

You say " XLSX file will change the variables". Do you know the names, types and characteristics of all of the variables that will ever appear? If not, you may want to go back to who is building this process and say that this is going to be a lot of human required maintenance because every time a new variable is encountered your entire process is subject to breakage. Or if the first time a variable is used it is treated as numeric but later is character (or vice versa).  If you are actually using Proc Import or one of the wizards to read XLSX data you will encounter problems combining data sets because there is nothing in XLSX that will keep a consistent behavior for a given named variable(column).

Search the forum for key words Import and Excel to find out how frequent this sort of problem might be.