ERROR: Character expression requires a character format.

[sarahzhou]

Hi, 

I want to select the date as yymmddd10. format from "my_output table"

my_output table is

OrderID1	OrderDate1	OrderID2	OrderDate2
A0000123	4/30/2010	A0000124

proc sql noprint;
	select compress("'"||%nrquote(OrderID1)||"'") into: OrderID from my_output;	

	select OrderDate1 informat=yymmddd10. into: OrderDate from my_output;
run;

After I run the script, I have the error:

ERROR: Character expression requires a character format.

The OrderDate1 is a character type

How can I fix the error?

Could you please advise?

Thanks

[PaigeMiller]

Please do not show us ERRORs in the log detached from the code that appears in the log. Please do show us the log for the PROC or DATA step that produces the error, do not cut parts out, show us the full complete unedited log for the PROC or DATA step. We need to see the code as it appears in the log, plus all of the ERRORs, WARNINGs and NOTEs for this PROC or DATA step.

Please copy the log as text and then paste it into the window that appears when you click on the </> icon.

[Tom]

The error message does not appear to match the code you posted.

In the first statement the macro quoting function is doing nothing as none of the characters in the variable name ORDERID1 requires any macro quoting.  The variable ORDERID1 is clearly character from the photograph of the data you provided so you could reference its value with the concatenation operators without any issue.

The second statement does not make much sense.  Telling SQL to attach an INFORMAT to a variable during a select that is not used to create an actual dataset will do nothing. 

What are you actually trying to do?  Why would you want to put the quoted value of ORDERID1 from the first observation of the dataset MY_OUTPUT into a macro variable named ORDERID?  What about the values in the other observations in your picture?  Do those need to be put into macro variable also?  What do you plan to do with the macro variable?

[sarahzhou]

This is the full code

lename j temp;

%let input_string ='{"OrderID1": "A0000123", "OrderDate1": "4/30/2010","OrderID2": "A0000124", "OrderDate2": ""}';

data _null_;
file j;
put &input_string;

run;

libname my_data json fileref=j;

data my_output;
 set my_data.root (drop=ordinal:);
run;

/*After run above code the output will be*/
/************************************************/
/*OrderID1	OrderDate1	OrderID2	OrderDate2*/
/*A0000123	4/30/2010	A0000124	*/

proc print data=my_output;
run;

proc sql;
	select compress("'"||%nrquote(OrderID1)||"'") into: OrderID from my_output;	

	select OrderDate1 informat=yymmddd10. into: OrderDate from my_output;
run;

proc sql;

create table member1 as 
select OrderID1
from output;
quit;

And the full log,

1                                                                                                                        The SAS System                                                                                            22:09 Friday, January 7, 2022

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='write_two_line_JSON_File.sas';
4          %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='C:\Users\P1341095\Documents\00 MSF\write_two_line_JSON_File.sas';
9          %LET _SASPROGRAMFILEHOST='NCS-5CG12517WL';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         %macro HTML5AccessibleGraphSupported;
14             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
15         %mend;
16         FILENAME EGSR TEMP;
17         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
18             STYLE=HTMLBlue
19             NOGTITLE
20             NOGFOOTNOTE
21             GPATH=&sasworklocation
SYMBOLGEN:  Macro variable SASWORKLOCATION resolves to "/mnt/resource/SAS_work52C100005B5C_MSFCAV94/SAS_work19AF00005B5C_MSFCAV94/"
22             ENCODING=UTF8
23             options(rolap="on")
24         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
25         
26         proc sql;
27         	select compress("'"||%nrquote(OrderID1)||"'") into: OrderID from my_output;
27       !                                                                             	
28         
29         	select OrderDate1 informat=yymmddd10. into: OrderDate from my_output;
ERROR: Character expression requires a character format.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
30         run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
31         
32         %LET _CLIENTTASKLABEL=;
33         %LET _CLIENTPROCESSFLOWNAME=;
34         %LET _CLIENTPROJECTPATH=;
35         %LET _CLIENTPROJECTPATHHOST=;
36         %LET _CLIENTPROJECTNAME=;
37         %LET _SASPROGRAMFILE=;
38         %LET _SASPROGRAMFILEHOST=;
39         
40         ;*';*";*/;quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
40       !                run;
41         ODS _ALL_ CLOSE;
42         
43         
44         QUIT; RUN;
45         

[sarahzhou]

Sorry, the first line is
filename j temp;

[ballardw]

Some questions to consider:

Why are you using an INFORMAT at all in a select into?

Why are you using an informat that attempts to read a year first, informat=yymmdd10,  with a value, 4/30/2010, that obviously is month, day, year ?

If you want to use an informat you must tell SAS to use it:

data junk;
   orderdate1="4/30/2010";
   number=1234;
run;

proc sql;
select input(OrderDate1,mmddyy10.) into: OrderDate from junk;
quit;

%put Orderdate is:&orderdate;

[sarahzhou]

But later I want to check whether the "orderdate1" is empty, I did

data junk;
   orderdate1="4/30/2010";
   number=1234;
run;

proc sql;
select input(OrderDate1,mmddyy10.) into: OrderDate from junk;
select 
		case when OrderDate1 ne . then 1
		else 0 end into: OrderDate_IND from junk;
quit;

%put Orderdate is:&orderdate;

And I get error,

1                                                                                                                        The SAS System                                                                                            22:09 Friday, January 7, 2022

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='write_two_line_JSON_File.sas';
4          %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='C:\Users\P1341095\Documents\00 MSF\write_two_line_JSON_File.sas';
9          %LET _SASPROGRAMFILEHOST='NCS-5CG12517WL';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         %macro HTML5AccessibleGraphSupported;
14             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
15         %mend;
16         FILENAME EGSR TEMP;
17         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
18             STYLE=HTMLBlue
19             NOGTITLE
20             NOGFOOTNOTE
21             GPATH=&sasworklocation
SYMBOLGEN:  Macro variable SASWORKLOCATION resolves to "/mnt/resource/SAS_work52C100005B5C_MSFCAV94/SAS_work19AF00005B5C_MSFCAV94/"
22             ENCODING=UTF8
23             options(rolap="on")
24         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
25         


26         data junk;
27            orderdate1="4/30/2010";
28            number=1234;
29         run;

NOTE: Compression was disabled for data set WORK.JUNK because compression overhead would increase the size of the data set.
NOTE: The data set WORK.JUNK has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

30         
31         proc sql;
32         select input(OrderDate1,mmddyy10.) into: OrderDate from junk;
33         select
34         		case when OrderDate1 ne . then 1
35         		else 0 end into: OrderDate_IND from junk;
ERROR: Expression using not equals (^=) has components that are of different data types.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
36         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
SYMBOLGEN:  Macro variable ORDERDATE resolves to    18382
37         
38         %put Orderdate is:&orderdate;
2                                                                                                                        The SAS System                                                                                            22:09 Friday, January 7, 2022

Orderdate is:   18382
39         
40         %LET _CLIENTTASKLABEL=;
41         %LET _CLIENTPROCESSFLOWNAME=;
42         %LET _CLIENTPROJECTPATH=;
43         %LET _CLIENTPROJECTPATHHOST=;
44         %LET _CLIENTPROJECTNAME=;
45         %LET _SASPROGRAMFILE=;
46         %LET _SASPROGRAMFILEHOST=;
47         
48         ;*';*";*/;quit;run;
49         ODS _ALL_ CLOSE;
50         
51         
52         QUIT; RUN;
53         

How do I fix it?

Thanks!

[Kurt_Bremser]

orderdate1="4/30/2010";

By storing a string into your variable, you implicitly define it as character. A character value cannot be compared to the numeric missing value (.) In SQL.

What do you intend to achieve with your code? To just set a macro variable to some text, you only need one %LET statement.

[sarahzhou]

OrderID1 and OrderDate1 are for customer1 in other database, I want to set this specific customer information as macro variable, then I can pull information from other tables in different databases.

OrderID2 and OrderDate2 is for customer2.

The goal of ther whole big script is to find the relationship between these two customer , eg: whether they are in the same company/team and who is their reporting officer, who is the senior level over the other one.

[Kurt_Bremser]

You need a dataset with three columns:

1. an identification of your customer
2. orderid
3. orderdate

Join this dataset with your database table on orderid and orderdate, and you get your desired information. No macro variables needed.

Make sure that orderid and orderdate match their counterparts in the database in terms of types and attributes (length, format).

[Tom]

To combine data from two different dataset there is no need to resort to code generation.

Just use a data step MERGE or an SQL JOIN.

For example if you have a dataset name CUSTOMERS with variables NAME and ORDERID you might want to use code like this:

proc sql noprint;
  create table want as
  select a.orderid1
       , input(a.orderdate1,mmddyy10.) as orderdate1 format=yymmdd10.
       , a.orderid2
       , b.name as name1
       , input(a.orderdate2,mmddyy10.) as orderdate2 format=yymmdd10.
       , c.name as name2
   from my_data.root a
   left join customers b on a.orderid1 = b.orderid
   left join customers c on a.orderid2 = c.orderid
   ;
quit;

[Tom]

You should probably forget about macro variables and the macro language.

The macro language is for programmatically generating SAS code. So you need to understand how SAS code works to able to know what code you want to generate.  

So what is the goal?

Are you trying to convert your JSON string into data?

filename my_data temp;

data _null_;
  file my_data;
  put '{"OrderID1": "A0000123", "OrderDate1": "4/30/2010","OrderID2": "A0000124", "OrderDate2": ""}';
run;

libname my_data json ;

proc print data=my_data.root;
run;

proc contents data=my_data.root;
run;

Results:

            ordinal_                  Order                  Order
     Obs      root      OrderID1      Date1      OrderID2    Date2

       1        1       A0000123    4/30/2010    A0000124

The CONTENTS Procedure

Data Set Name        MY_DATA.ROOT    Observations          .
Member Type          DATA            Variables             5
Engine               JSON            Indexes               0
Created              .               Observation Length    34
Last Modified        .               Deleted Observations  0
Protection                           Compressed            NO
Data Set Type                        Sorted                NO
Label
Data Representation  Default
Encoding             Default


Alphabetic List of Variables and Attributes

#    Variable        Type    Len

3    OrderDate1      Char      9
5    OrderDate2      Char      1
2    OrderID1        Char      8
4    OrderID2        Char      8
1    ordinal_root    Num       8

Do you want to convert those two date variables into actual DATE values?

data want;
  set my_data.root;
  orderdt1 = input(orderdate1,??mmddyy10.);
  orderdt2 = input(orderdate2,??mmddyy10.);
  format orderdt1 orderdt2 yymmdd10.;
  drop ordinal_root;
run;

Results

                     Order                  Order
Obs    OrderID1      Date1      OrderID2    Date2      orderdt1      orderdt2

 1     A0000123    4/30/2010    A0000124             2010-04-30             .

[sarahzhou]

Hi,

How do I keep my output column as I wanted?

data want;
  set my_data.root;
  orderdt1 = input(orderdate1,??mmddyy10.);
  orderdt2 = input(orderdate2,??mmddyy10.);
  format orderdt1 orderdt2 yymmdd10.;
  keep OrderID1 orderdt1 OrderID2 orderdt2;
run;

I get this,

But I want my column be arranged like this

OrderID1	orderdt1	OrderID2	orderdt2
A0000123	4/30/2010	A0000124	.

How do I modify the code?

Thanks

[Tom]

---

@sarahzhou wrote:

Hi,

How do I keep my output column as I wanted?

data want;
  set my_data.root;
  orderdt1 = input(orderdate1,??mmddyy10.);
  orderdt2 = input(orderdate2,??mmddyy10.);
  format orderdt1 orderdt2 yymmdd10.;
  keep OrderID1 orderdt1 OrderID2 orderdt2;
run;

I get this,

But I want my column be arranged like this

OrderID1	orderdt1	OrderID2	orderdt2
A0000123	4/30/2010	A0000124	.

How do I modify the code?

Thanks

---

Huh? What does the order of the variables in the dataset have to do with anything?

You can print the variables in any order you want, just list them in the VAR statement of PROC PRINT in the order you want.

proc print;
  var orderid1 orderdt1 orderid2 orderdt2;
run;

You can set the order of variables in a dataset by defining them explicitly.

data want;
   length orderid1 $9 orderdt1 8 orderid2 $9 orderdt 8;
   set have;
run;

You can even use a trick of using a RETAIN statement to set the order as that will not force the data step compiler to decide the type and length of the variables.

data want;
   retain orderid1 orderdt1 orderid2 orderdt;
   set have;
   .....
run;

[Tom]

The order is set by when the compiler first sees the variable being used.

Since you already have a KEEP statement that is listing all of the variables then make that the first statement and it will force the order.

data want;
  keep OrderID1 orderdt1 OrderID2 orderdt2;
  set my_data.root;
  orderdt1 = input(orderdate1,??mmddyy10.);
  orderdt2 = input(orderdate2,??mmddyy10.);
  format orderdt1 orderdt2 yymmdd10.;
run;