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

Hi All,

 

 

I am imported excel file(I used Options validvarname=any before importing excel file) ,after that I am keeping only selected columns by using keep statement in data set.

But for 2 variable I am getting warning.

 

My code is:

Data LCS_i;
set LCS_i ;
keep 'LCS NUMBER'n DESIGNATION 'RATED POWER'n 'RATED CURRENT (A)'n TYPE 'CONTROL CABLE SIZE
(Sq.mm)'n
'CABLE TYPE'n 'STATUSP / F'n DM REMARKS Rev prj_id;
run;

 

Warning is:

WARNING: The variable 'CONTROL CABLE SIZE(Sq.mm)'n in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable 'STATUSP / F'n in the DROP, KEEP, or RENAME list has never been referenced.

 

I tried to resolve this thing . I tried in keep and rename statement with no luck.

 

Can anyone explain me how to resolve this issue?

 

Thanks,

Nirav Chaudhari

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

CONTENTS does not wrap the names in the output:

options validvarname=any;

data have;
"CONTROL CABLE SIZE (Sq.mm)"n = "1234567890123";
run;

proc contents data=have;
run;

See this result I copy/pasted directly into a window opened with the </> button:

Alphabetische Liste der Variablen und Attribute
#	Variable	Typ	Länge
1	CONTROL CABLE SIZE (Sq.mm)	Char	13

It is important to see the exact number of blanks (and possibly other characters) in the names. You might even have to rerun CONTENTS with an OUT= option, and later convert the name to a HEX string so you can see any funny characters (like UTF characters that look similar to the single-byte characters you type, but aren't).

 

I STRONGLY suggest that you rerun the import with VALIDVARNAME=V7 and let SAS make proper names out of those strings. Valid SAS names are MUCH easier to handle in code than the "name literal" crap.

View solution in original post

17 REPLIES 17
PaigeMiller
Diamond | Level 26

Your data set named LCS_I does not have variables by those names. Therefore, you can't include those variables in a KEEP statement.

 

You need to actually LOOK AT the data set with your own eyes to see what variable names are actually in the data set.

 

By the way, using this type of code, where the data set name is the same as the name in the SET statement is not recommended; and if you run the DATA step twice, you will get the error you see, even in the case where those variables were present originally.

--
Paige Miller
NiravC
Obsidian | Level 7

Hi

 

Both Variables  are available in  LCS_I. That's why I am confuse, why it's showing warning.

 

Thanks

PaigeMiller
Diamond | Level 26

@NiravC wrote:

Both Variables  are available in  LCS_I. That's why I am confuse, why it's showing warning.


My rule is that if SAS says the variable is not in the data set, and the user says the variable is in the data set, then I believe SAS.

 

Show us what you see. Convince us that the variables are there. Give us information. Re-write your program as @Kurt_Bremser and I suggested. 

--
Paige Miller
Kurt_Bremser
Super User

First of all, it is never a good idea to do this:

Data LCS_i;
set LCS_i ;

If something untoward happens that does not stope the data step from executing, you destroy your dataset and need to create it anew.

Especially while developing, create a new dataset:

Data LCS_i_new;
set LCS_i;

Regarding your WARNINGs: the variables are not there in the dataset. Look at the variables as shown in the viewtable, or run a PROC CONTENTS.

NiravC
Obsidian | Level 7

Hii Kurt,

 

Thanks for answering, But I tried this thing :

Data LCS_i_new;
set LCS_i;

It's not working.

And I run  the Proc Contents, Both the variable available with same name which is available in my early post.

 

Thnks

NiravC
Obsidian | Level 7

Hii Kurt,

 

See this Proc Contents Output, Both Variables are available , even I can show the data in output also , but I am not able write this two in Keep,Drop and Rename statement or option.

Alphabetic List of Variables and Attributes

# Variable Type Len Format Informat Label
13 CONTROL CABLE SIZE (Sq.mm) Char 13 $13.00 $13.00 CONTROL CABLE SIZE (Sq.mm)
108 STATUS P / F Char 9 $9.00 $9.00 STATUS P / F
Kurt_Bremser
Super User

CONTENTS does not wrap the names in the output:

options validvarname=any;

data have;
"CONTROL CABLE SIZE (Sq.mm)"n = "1234567890123";
run;

proc contents data=have;
run;

See this result I copy/pasted directly into a window opened with the </> button:

Alphabetische Liste der Variablen und Attribute
#	Variable	Typ	Länge
1	CONTROL CABLE SIZE (Sq.mm)	Char	13

It is important to see the exact number of blanks (and possibly other characters) in the names. You might even have to rerun CONTENTS with an OUT= option, and later convert the name to a HEX string so you can see any funny characters (like UTF characters that look similar to the single-byte characters you type, but aren't).

 

I STRONGLY suggest that you rerun the import with VALIDVARNAME=V7 and let SAS make proper names out of those strings. Valid SAS names are MUCH easier to handle in code than the "name literal" crap.

NiravC
Obsidian | Level 7

Hi Kurt,

 

I changed options valivarname=any to v7 as per your said,

Now 'CONTROL CABLE SIZE (Sq.mm)'n is showing VAR13 &
 'STATUS P / F'n is showing STATUS_P___F.

 

Thanks for giving me a best solution. Now, It's working fine.

 

Thanks,

Nirav Chaudhari

 

NiravC
Obsidian | Level 7

Yes, you are right. Label is showing original content.

Kurt_Bremser
Super User

My personal preference, especially for processes that must be repeatable, is this:

  • save Excel files CSV files
  • write a data step to read the data, defining my own names
  • if necessary, take the contents of the header line as labels

For instance, I would use cont_cbl_size as the variable name; long enough to be descriptive, but short enough to reduce typing errors and the sheer number of keyboard hits while coding.

NiravC
Obsidian | Level 7

Hi Kurt,

 

Again I am facing this issue with other data and I follow this solution but options validvarname=v7 is not supported for columns. So, I can't able to see proc contents things.

 

Can you help me out in this issue?

 

Thanx,

Nirav

Kurt_Bremser
Super User

The VALIDVARNAME option is supported in ALL SAS environments, and can be set anytime. Please post your complete log, from OPTIONS VALIDVARNAME=V7 to the data step with which you read the data.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 17 replies
  • 3721 views
  • 10 likes
  • 4 in conversation