I am new to SAS and I am currently trying to write a program that uses Proc SQL; to create a table. After the table is created, I am trying to call variable/fields from that table in SCL to use later. Below is the section of code that is getting the error (names of tables and variables have been changed), and the error itself. Any advice on what I'm doing wrong would be greatly appreciated.
SUBMIT continue; proc sql; create table work.TestCount as select * , count(*) as field3 from sasTest.table1 where field1 ne ""; quit; endsubmit; refTest='work.TestCount'; FieldTest=open(refTest,'u'); TestField1=getvarc(FieldTest,varnum(FieldTest,field1)); <-- ERROR IS STOPPING PROGRAM HERE TestField2=getvarc(FieldTest,varnum(FieldTest,field2)); TestField3=getvarn(FieldTest,varnum(FieldTest,field3));
*** Line 130: [ERROR: #70] Calling function GETVARC. ERROR: Variable is not defined or does not exist.
Arguments passed to GETVARC:
1 FieldTest = 3
2 (Numeric Expression $T0) = 0
Program returning prematurely at line 130
There are a couple things happening with the GETVARC function in your code:
1 - The way you have the GETVARC function defined:
TestField1=getvarc(FieldTest,varnum(FieldTest,field1));
This code is expecting the FIELD1 variable in the VARNUM function to be a character value. The way that you have this coded, FIELD1 is a variable that would need to contain the character value 'field1'. So, your GETVARC statements should look like this:
TestField1=getvarc(FieldTest,varnum(FieldTest,'field1'));
2 - Just making the above change is not enough to make the code work though (you will get a different error message). With the GETVARC/N functions, you need to make an observation from the table available. This is done with the FETCH or FETCHOBS function. So, the code after your SUBMIT block would need to look like this:
...
endsubmit;
refTest='work.TestCount';
FieldTest=open(refTest,'u');
rc=fetch(FieldTest);
TestField1=getvarc(FieldTest,varnum(FieldTest,'field1'));/* <-- ERROR IS STOPPING PROGRAM HERE */
TestField2=getvarc(FieldTest,varnum(FieldTest,'field2'));
TestField3=getvarn(FieldTest,varnum(FieldTest,'field3'));
Let me know if you have any problems or questions.
Thanks,
Greg
This is a very interesting question. Please try replacing this fragment
refTest='work.TestCount';
FieldTest=open(refTest,'u');
TestField1=getvarc(FieldTest,varnum(FieldTest,field1));
with this one:
dsid = open( "work.TestCount", "i" );
position = varnum( dsid, field1 );
TestField1 = getvarc( dsid, position );
References
[2] Open() function
Thanks for replying. This is unfortunately giving a similar error on the Getvarc again.
Here is a copy of the error:
*** Line 130: [ERROR: #70] Calling function GETVARC. ERROR: Variable is not defined or does not exist.
Arguments passed to GETVARC:
1 dsid = 3
2 TestField1 = 0
Program returning prematurely at line 130
What happens if you type
TestField1 = getvarc( dsid, 1 );
It seems to be having trouble reading in any observations from the table even with the 1 hardcoded in.
Error:
ERROR: No observations have been read from data set WORK.TESTCOUNT.
Arguments passed to GETVARC:
1 dsid = 3
2 (Numeric Literal) = 1
Program returning prematurely at line 130
@gregl_sas helped me nicely with my SCL question:
Maybe this person can have a look at this question, too.
Thanks for the attempts, @pink_poodle !
There are a couple things happening with the GETVARC function in your code:
1 - The way you have the GETVARC function defined:
TestField1=getvarc(FieldTest,varnum(FieldTest,field1));
This code is expecting the FIELD1 variable in the VARNUM function to be a character value. The way that you have this coded, FIELD1 is a variable that would need to contain the character value 'field1'. So, your GETVARC statements should look like this:
TestField1=getvarc(FieldTest,varnum(FieldTest,'field1'));
2 - Just making the above change is not enough to make the code work though (you will get a different error message). With the GETVARC/N functions, you need to make an observation from the table available. This is done with the FETCH or FETCHOBS function. So, the code after your SUBMIT block would need to look like this:
...
endsubmit;
refTest='work.TestCount';
FieldTest=open(refTest,'u');
rc=fetch(FieldTest);
TestField1=getvarc(FieldTest,varnum(FieldTest,'field1'));/* <-- ERROR IS STOPPING PROGRAM HERE */
TestField2=getvarc(FieldTest,varnum(FieldTest,'field2'));
TestField3=getvarn(FieldTest,varnum(FieldTest,'field3'));
Let me know if you have any problems or questions.
Thanks,
Greg
Hi Greg,
This worked perfectly. Thanks so much!
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.