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

Hi All

I am creating a variable using proc sql :into statement from Table . Table having String in Chinese language , I am able to create variable but whenever I am try to insert this variable in a other table ( by proc sql & data step ) or want to print on log using %put then below Error is coming

 

ERROR: The value ',高於上季的上升'n is not a valid SAS name.

WARNING: Apparent invocation of macro ,高於上季的上升 not resolved.

ERROR: The value ',與上季相同'n is not a valid SAS name.

WARNING: Apparent invocation of macro ,與上季相同 not resolved.

 

Note :- I am not able to see “ “  and “ ‘n “ value in table but it showing in log Error.

I have upload this data from Source file (.xlsx) using UTF-8 session (on SAS EG & SAS AC) and running my current code under UTF-8 session only. Below is the some part of that string

高於上季的上升 1.9% 與上月相同 (-) 澳洲

 

I am able to insert data in other table but above Error coming. I want to stop this error message

1 ACCEPTED SOLUTION

Accepted Solutions
anandsasbi
Obsidian | Level 7

Hi KurtBremser,

Thanks for your suggestion, Now I have change my code based on you suggestion ( exclude OBS and use Data step for Pick value from Table fix position ) . Now I am not getting any error.

Thanks Again J

 

data AUH (Keep = Currency Currency_Type SpotLevel VS_HKD Daily_Change header Footer );

retain Currency Currency_Type SpotLevel VS_HKD Daily_Change header Footer ;

length Currency $4. Currency_Type $8. SpotLevel 8. VS_HKD 8. Daily_Change 8. header $400. Footer $400. ;

format   SpotLevel 10.4 VS_HKD 10.4 Daily_Change 8.2;

 

set test2 end=done;

if _n_ = 9 then do; Currency_Type = b ; SpotLevel= input(Strip(f),8.); end;

if _n_ = 10 then do; Currency = SUBSTR(Strip(b),1,3) ; VS_HKD = input(Strip(f),8.) ; end;

if _n_ = 11 then Daily_Change = Input(strip(Substr(f, 1 , length(f)-1)),8.);

if _n_ = 12 then header = b;

if _n_ = 14 then footer = b;

if done then output;

run;

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

You would need to at minimum show the code you are running.  The part:

'....'n

Refers to a named literal.  It is often used when gettting data from something which doesn't conform to SAS standards, Excel for instance is notorious for it.  You can name ranges or sheets anything, and 'XYZ  something' is not a valid SAS name, so you use the named literal structure to refer to it.  This would seem to be what is happening here, when SAS tries to read the data from Excel it is finding ranges or sheet names it can't deal with, so is putting them into named literals, though as I can't see your code I can't see what you are doing with them.

 

So post your code for some suggestions, or a far, far better idea is to drop the data from Excel into a useable format, CSV for instance can be saved from Excel, then you can write a proper datastep import routine to read teh data correctly - avoids all the Excel nonsense.

anandsasbi
Obsidian | Level 7

Hi RW9,

 

Thanks for reply,

 

Source team has provide us .xlsx file and this .xlsx file having multiple graph whit header & footer for each graph. I am reading header & footer data which available in fixed cell Position. If I will convert it into CSV file than graph and Cell position will be lost. But In .xlsx file and SAS table (like WORK.Test2) I am not able to see “” & “’n”  . it’s coming only in log Error.

 Below code i am using under UTF-8 Session.

%LET SHEETNAME = "10 sec pitch Staff";

PROC IMPORT OUT= WORK.Test2 (keep=B D F R T V X ) DATAFILE= "/UnixPath/Source/external_Trad_Chinese_value_sample.xlsx"

           DBMS=XLSx REPLACE;

     SHEET=&SHEETNAME.;

     GETNAMES=YES;

RUN;

proc sql;

create table Test2_Obs as

select monotonic() as obs,B , D , F, R ,T ,V , X from WORK.Test2;

quit;

 

proc sql;

select b into: AUD_Header from Test2_Obs where obs=12;

select tranwrd(b, "(~)", " ") as b1 length=400 into: AUD_Footr

from Test2_Obs where obs=14;

quit;

 

proc sql;

create table FX_Market_T

       (

     header Char(400),

       Footer char(400)

       );

quit;

 

proc sql;

/* In Insert Statement Error is coming */

insert into work.FX_Market_T

              values(“AUD_Header","&AUD_Footr")

quit;

for below string I am getting ' & 'n error but in string it's not showing . In Particulr Cell having 4 lines

高於上季的上升 1.9% 與上月相同 (-) 澳洲

  Please find Screeshot of Souce Cell from .xlsx file

 


4Line in Fix Cell position.png
Kurt_Bremser
Super User

If the whole purpose of this overcomplicated code is the creation of a dataset with one obs containing header and footer text, consider this:

data fx_market_t (keep=header footer);
retain header footer;
length header footer $400;
set test2 end=done;
if _n_ = 12 then header = b;
if _n_ = 14 then footer = b;
if done then output;
run;

Since the string with the percent sign never appears in program code (something that invariably happens when you use macro variables), you do not have the masking problem.

As a reference for your problem, see this:

data test;
input string $30.;
cards;
%thés is some data
;
run;

proc sql noprint;
select string into :macvar from test;
create table new (newstring char(30));
insert into new values("&macvar");
quit;

which causes the same ERROR and WARNING

Kurt_Bremser
Super User

You put data that contains a percent sign into a macro variable. When you use the value from that macro variable without masking the percent sign, the macro processor will kick in unexpectedly. Please show how you use that macro variable, from the proc sql with the into: to the step that causes the error message.

anandsasbi
Obsidian | Level 7

Hi KurtBremser,

Thanks for your suggestion, Now I have change my code based on you suggestion ( exclude OBS and use Data step for Pick value from Table fix position ) . Now I am not getting any error.

Thanks Again J

 

data AUH (Keep = Currency Currency_Type SpotLevel VS_HKD Daily_Change header Footer );

retain Currency Currency_Type SpotLevel VS_HKD Daily_Change header Footer ;

length Currency $4. Currency_Type $8. SpotLevel 8. VS_HKD 8. Daily_Change 8. header $400. Footer $400. ;

format   SpotLevel 10.4 VS_HKD 10.4 Daily_Change 8.2;

 

set test2 end=done;

if _n_ = 9 then do; Currency_Type = b ; SpotLevel= input(Strip(f),8.); end;

if _n_ = 10 then do; Currency = SUBSTR(Strip(b),1,3) ; VS_HKD = input(Strip(f),8.) ; end;

if _n_ = 11 then Daily_Change = Input(strip(Substr(f, 1 , length(f)-1)),8.);

if _n_ = 12 then header = b;

if _n_ = 14 then footer = b;

if done then output;

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 5 replies
  • 6990 views
  • 2 likes
  • 3 in conversation