BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
manikanta9500
Calcite | Level 5

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   

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
ballardw
Super User

@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.

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 323 views
  • 1 like
  • 3 in conversation