topic Re: Problem with insert into in SAS Programming

Problem with insert into

Gieorgie — Sun, 28 Nov 2021 15:07:37 GMT

I have a problem, I want to extract from the library exchange rates between two dates for EUR and USD, but I have to add PLN = 1 to calculate other values. But when I try to insert into it, it shows me an error

proc sql;
	create table kursy as
	select 
			DATA_DANYCH,
			CURRENCY_SHORT_NAME				as WALUTA,
			REVAL_RATEMID					as KURS
	from kondor.K_FXRATES
	where DATA_DANYCH between '24Apr2016'd and &gv_date_kon.
	  and CURRENCY_SHORT_NAME in ("EUR", "USD")
	order by DATA_DANYCH
;

	insert into kursy values('REVAL_RATEMID','PLN', 1)
;
quit;

DATA_DANYCH	WALUTA	KURS
2021-04-21	EUR	4,5567
2021-04-21	USD	4,4222
2021-04-21	PLN	1

Above table is what i expect :

Error is below:

50 insert into kursy values('REVAL_RATEMID','PLN', 1)
2 System SAS 14:38 Sunday, November 28, 2021

51 ;
ERROR: Value 1 of VALUES clause 1 does not match the data type of the corresponding column in the object-item list (in the SELECT
clause).

Re: Problem with insert into

PaigeMiller — Sun, 28 Nov 2021 15:31:00 GMT

Please do not show us error messages separated from the code. Please DO show us the entire log for this PROC SQL, every single line of the log for this PROC SQL, so we can see the code as it appears in the log; so we can see the NOTEs, WARNINGs and ERRORs as they appear in the log.

Please preserve the formatting of the log by pasting it as text into the window that appears when you click on the </> icon. DO NOT SKIP THIS STEP.

Re: Problem with insert into

Kurt_Bremser — Sun, 28 Nov 2021 15:41:58 GMT

data_danych is a date, and you try to insert the string REVAL_RATEMID in the second INSERT.

Re: Problem with insert into

Gieorgie — Mon, 29 Nov 2021 07:09:47 GMT

but when i tried put like this insert into kursy values('DATA_DANYCH',"PLN", 1)
i got same error

Re: Problem with insert into

andreas_lds — Mon, 29 Nov 2021 07:47:25 GMT

@Gieorgie wrote:
but when i tried put like this insert into kursy values('DATA_DANYCH',"PLN", 1)
i got same error

Rephrasing what Kurt already said: your first variable is a date, dates can't be set to a string-value.

Re: Problem with insert into

Kurt_Bremser — Mon, 29 Nov 2021 07:58:01 GMT

Repeating the same action over and over and expecting a different result is a sure sign of insanity (Einstein).

You CANNOT insert a string into a numeric variable.

Re: Problem with insert into

Gieorgie — Mon, 29 Nov 2021 08:08:20 GMT

Ok i ubderstand, but is there any chance to put PLN =1 to each date?

Re: Problem with insert into

Kurt_Bremser — Mon, 29 Nov 2021 11:34:12 GMT

Supply usable example data. Usable = a working data step with datalines, posted in a code box. This is essential to enable us to test our code suggestions and MUST NOT BE SKIPPED.
Show what you expect to get out of this.

Re: Problem with insert into

Gieorgie — Mon, 29 Nov 2021 12:29:33 GMT

im trying do from your data steps instruction %data2datastep(kursy,work,,5) but i got error :WARNING: Apparent invocation of macro DATA2DATASTEP not resolved.
27
28 %data2datastep(kursy,work,,5)
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
I dont know why

Re: Problem with insert into

Kurt_Bremser — Mon, 29 Nov 2021 12:33:49 GMT

In order to use the macro, you must first run the code that defines it.

Re: Problem with insert into

Gieorgie — Mon, 29 Nov 2021 12:52:14 GMT

Im used download macro and i get like this ;

28         %data2datastep(kursy,work,5)
NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

DATA2DATASTEP DSN KURSY
DATA2DATASTEP FILE create_5_KURSY_data.sas
DATA2DATASTEP FMT YES
DATA2DATASTEP FMTLIST DATA_DANYCH YYMMDD10.
DATA2DATASTEP INPUTLIST DATA_DANYCH:YYMMDD10. WALUTA:$100. KURS:32.
DATA2DATASTEP LBL YES
DATA2DATASTEP LIB WORK
DATA2DATASTEP MEMLABEL 
DATA2DATASTEP MSGTYPE NOTE
DATA2DATASTEP OBS MAX
DATA2DATASTEP OUTLIB 5
DATA2DATASTEP VARLIST DATA_DANYCH WALUTA KURS

I have like here:

DATA_DANYCH	WALUTA	KURS
2021-04-21	EUR	4,5567
2021-04-21	USD	4,4222

and I would like to add PLN = 1 to each date

DATA_DANYCH	WALUTA	KURS
2021-04-21	EUR	4,5567
2021-04-21	USD	4,4222
2021-04-21	PLN	1

Re: Problem with insert into

andreas_lds — Mon, 29 Nov 2021 14:41:48 GMT

Drop insert, it won't work in your case, use a data step instead. Untested code:

data want;
  set kursy;
  by data_daynch;
  output;
  if last.data_danych then do;
    waluta = "PLN";
    kurs = 1;
    output;
  end;
run;

Re: Problem with insert into

Tom — Tue, 30 Nov 2021 05:49:57 GMT

So you seem to have clarified your request.

I would like to add PLN = 1 to each date

Why not just add them when you create the dataset?

create table kursy as

  select DATA_DANYCH
       , CURRENCY_SHORT_NAME as WALUTA
       , REVAL_RATEMID as KURS
  from kondor.K_FXRATES
  where DATA_DANYCH between '24Apr2016'd and &gv_date_kon.
    and CURRENCY_SHORT_NAME in ("EUR", "USD")

  union

  select distinct 
         DATA_DANYCH
       , 'PLN' as WALUTA
       , 1 as KURS
  from kondor.K_FXRATES
  where DATA_DANYCH between '24Apr2016'd and &gv_date_kon.
    and CURRENCY_SHORT_NAME in ("EUR", "USD")

  order by DATA_DANYCH
;

Now if you want to add a record for every date from '24Apr2016'd to &gv_date_kon rather than just those that appear in kondor.K_FXRATES then you might want a different approach.

data extra ;
  if 0 then set kursy ;
  WALUTA = 'PLN' ;
  KURS=1;
  do DATA_DANYCH =  '24Apr2016'd to  &gv_date_kon. ;
    output;
  end;
run;
proc append base=kursy data=extra;
run;