topic Re: UNABLE TO RESOLVE BACKEND VALUE IN EXCEL INTO SAS in SAS Programming

UNABLE TO RESOLVE BACKEND VALUE IN EXCEL INTO SAS

rohithverma — Wed, 15 Apr 2020 18:01:38 GMT

Hi All,

I had a value in csv file where the backend data within that value contain two parts.How can i resolve those internal values and read into sas .

Please find the screen shot below.

Re: UNABLE TO RESOLVE BACKEND VALUE IN EXCEL INTO SAS

ballardw — Wed, 15 Apr 2020 18:59:23 GMT

How did you read the csv file? If using a data step then read that column as character and parse the value if you need the two numbers. You don't really show us what you expect for the output.

Unless you are careful you may no longer have the same values after opening the file in Excel if you saved it afterwards.

Re: UNABLE TO RESOLVE BACKEND VALUE IN EXCEL INTO SAS

RichardDeVen — Thu, 16 Apr 2020 00:37:57 GMT

The dynamic resolution of an expression at DATA step run-time can be accomplishing by RESOLVEing the expression in the macro environment with the %SYSEVALF macro function. Note the leading equals sign (=) will need to be ignored for proper evaluation. RESOLVE returns the character string representing the formula result, so that return also needs to be INPUT.

Example:

data x;
  remarks = '=1/7';

  if remarks =: '=' 
    then remarks_eval = input(resolve ('%sysevalf(' || substr(remarks,2) || ')' ), best12.);

  x = 1 / 7;
  delta = x - remarks_eval;

  put x= remarks_eval= delta=;
run;

Note: Some very small differences may be noticed between the actual ratio and the macro resolution of the ratio.

           x=0.1428571429
remarks_eval=0.1428571428
       delta=5.714285E-11

Re: UNABLE TO RESOLVE BACKEND VALUE IN EXCEL INTO SAS

fdsaaaa — Thu, 16 Apr 2020 02:47:00 GMT

It can't be done as far as I can see. The screenshot is from excel . But if you save as .csv then you can no longer get the formula used to create the value in the cell . Try opening the csv up again in excel and you will see there is no longer a formula . This is all before it has even touched SAS .

Re: UNABLE TO RESOLVE BACKEND VALUE IN EXCEL INTO SAS

Kurt_Bremser — Thu, 16 Apr 2020 06:37:17 GMT

Did you start with an Excel file and saved it from Excel to CSV, or did you start with a CSV file and opened it in Excel for viewing?