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

I wanted to insert three new character rows to the top of a numerical columns data set. When I tried to insert using   proc sql insert into set , i got the following error

 

ERROR: It is invalid to assign a character expression to a numeric value using the SET clause.

 

Existing data set:

Month    Net_sales    revenue

1              5454             4556

2              759656        877979

3              87874           24589

 

 

Expected Result:

                     Month   Net_sales    revenue

(new rows)    axis         A                   B

                     type          a1                 b1

                       1              5454             4556

                       2              759656        877979

                       3              87874           24589

 

 

I tried changing the format of existing data set to character

using below code 

Data Want;
set have;
MONTH_new = PUT(MONTH,$19.);

drop MONTH;

rename MONTH_NEW = MONTH;

run;

 

and 

proc sql;

insert into  table_name

set Month = 'Axis',

      Net_sales ='A'

Quit;

 

But the above query inserted the rows to the bottom while I need them in the top. Please help. 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Your problem is not insert so much as making sure that the values are all character.

 

We generally ask that data be presented as a data step so we can see what your variables really look like and so we have base data to provide code against.

You have found that SQL insert won't work as desired and I don't think you are going to find a ORDER BY that would work with a union.

 

Easier might be to put those header rows in separate data set and then combine with a SET statement and some extra logic to indicate that the values from the numeric values need to be converted to text in the same columns as the header.

 

data header;
   informat month net_sales revenue $19. ;
   input month net_sales revenue;
datalines;
axis         A                   B
type          a1                 b1
;
run;

data have;
   input month net_sales revenue;
datalines;
1              5454             4556
2              759656        877979
3              87874           24589
;
run;

data want;
   set
      header
      have (in=in2 rename=(month=month_num net_sales=net_sales_num 
            revenue=revenue_num))
   ;
   if in2 then do;
      month= put(month_num,f19. -L);
      net_sales= put(net_sales_num,f19. -L);
      revenue= put(revenue_num,f19. -L);
      drop month_num net_sales_num revenue_num;
   end;
run;

I used lengths of 19 as that was the only indication I had from your code as to likely lengths.

 

The IN= option creates a temporary variable that indicates when the current rows of data are coming from the HAVE set. The rename is remove the confusion of numeric vs character variable.

You can see how the temporary IN2 variable is used to signal converting the numeric values to character using the PUT function.

 

The -L in the PUT says to left justify the results.

 

You could change the 19 widths to whatever you want but the lengths in the header will be the default.

View solution in original post

5 REPLIES 5
ballardw
Super User

If a variable (column) is numeric you cannot have a character value for the variable in a SAS data set. Period. As the error says.

 

In fact what you propose is extremely difficult to work with any way if you did create it.

AXIS is not very useful for "month".

WHAT actual role would the value of Axis for Month or A for Net_sales play in further processing or reporting?

sreevatsan1991
Obsidian | Level 7

I need to feed the output of SAS in to another dashboard creation tool which expects input the  above mentioned format. It would be great if you let me know if we can insert rows to the top rather than bottom using proc sql insert into using set command.

ballardw
Super User

Your problem is not insert so much as making sure that the values are all character.

 

We generally ask that data be presented as a data step so we can see what your variables really look like and so we have base data to provide code against.

You have found that SQL insert won't work as desired and I don't think you are going to find a ORDER BY that would work with a union.

 

Easier might be to put those header rows in separate data set and then combine with a SET statement and some extra logic to indicate that the values from the numeric values need to be converted to text in the same columns as the header.

 

data header;
   informat month net_sales revenue $19. ;
   input month net_sales revenue;
datalines;
axis         A                   B
type          a1                 b1
;
run;

data have;
   input month net_sales revenue;
datalines;
1              5454             4556
2              759656        877979
3              87874           24589
;
run;

data want;
   set
      header
      have (in=in2 rename=(month=month_num net_sales=net_sales_num 
            revenue=revenue_num))
   ;
   if in2 then do;
      month= put(month_num,f19. -L);
      net_sales= put(net_sales_num,f19. -L);
      revenue= put(revenue_num,f19. -L);
      drop month_num net_sales_num revenue_num;
   end;
run;

I used lengths of 19 as that was the only indication I had from your code as to likely lengths.

 

The IN= option creates a temporary variable that indicates when the current rows of data are coming from the HAVE set. The rename is remove the confusion of numeric vs character variable.

You can see how the temporary IN2 variable is used to signal converting the numeric values to character using the PUT function.

 

The -L in the PUT says to left justify the results.

 

You could change the 19 widths to whatever you want but the lengths in the header will be the default.

sreevatsan1991
Obsidian | Level 7
I thought there would be any other easy way . Now you have reassured there ain't. Thanks for your confirmation.
Cynthia_sas
SAS Super FREQ

Hi:

  There IS an easy way, if you want a report output, such as ODS HTML, ODS RTF, ODS PDF or ODS Excel. It's PROC PRINT, as shown below:

Month_axis_proc_print.png

 

The SPLIT= option allows you to make a multi-line column header on report output. It is only more complicated if you want to put character values into a numeric column in a data set.

 

Cynthia

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