Not able to insert character rows to numerical columns using proc sql !

[sreevatsan1991]

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. 

[ballardw]

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]

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 · Accepted solution]

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]

I thought there would be any other easy way . Now you have reassured there ain't. Thanks for your confirmation.

[Cynthia_sas]

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:

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