Hi Iam getting the following error when I run the code
proc sql;
96 connect to ORACLE (user=rmin password=XXXXXXXXXX path=RMIN01D);
97 create table mcns as select * from connection to ORACLE (select ngid
from ngmaster where custno not like ‘ %’ and (ngupdate
97 ! not like ‘D’ and ngupdate not like ‘Y’) and ngid in (select ngid from
ngmaster where ngid in (select ngid from ngmaster where
98 custno in (
99 endrsubmit;
—
79
ERROR 79-322: Expecting a ).
Code:—-
submit remote continue;
proc sql;
&finalconn;
create table mcns as
select * from connection to &db_type
(select ngid from ngmaster where custno not like ‘ %’ and (ngupdate
not
like ‘D’ and ngupdate not like ‘Y’) and ngid in
(select ngid from ngmaster where ngid in
(select ngid from ngmaster where custno in
(
endsubmit;
do i = 1 to listlen(mcnlist);
custno =getitemc(mcnlist,i);
if i = listlen(mcnlist) then
mcn = «»‘»»||custno||»»‘»»;
else
mcn = «»‘»»||custno||»»‘,»»;
submit remote continue;
&mcn
endsubmit;
end;
submit remote continue;
) and (ngupdate not like ‘D’ and ngupdate not like ‘Y’)
)
and ngid not in
(select ngid from ngmaster where ( custno not in
(
endsubmit;
do i = 1 to listlen(mcnlist);
custno =getitemc(mcnlist,i);
if i = listlen(mcnlist) then
mcn = «»‘»»||custno||»»‘»»;
else
mcn = «»‘»»||custno||»»‘,»»;
submit remote continue;
&mcn
endsubmit;
end;
submit remote continue;
) and custno not like ‘ %’
) and (ngupdate not like ‘D’ and ngupdate not like ‘Y’)
) group by ngid
) group by ngid having count(ngid)=&mcnlen);
&disc_db;
quit;
proc download data=mcns out=mcns status=yes;
run;
endsubmit;
mcnsdsid=open(‘mcns’);
rc=fetchobs(mcnsdsid,1);
if rc ne -1 then do;
custno=»;
netgid=getvarc(mcnsdsid,varnum(mcnsdsid,’ngid’));
msg=’NetGroup ID «»‘||netgid||'»» already exists with the set of MCNs you ‘||
‘have selected. Please use this NetGroup ID.’;
put «»msg=»» msg=;
call display(‘info.frame’,msg);
alarm;
cursor ng;
end;
Please help me in this ..
Thanks,
Niranjan
Read these next…
IT managers — what non-technical job responsibilities do you struggle to manage?
Best Practices & General IT
As an IT manager, most of the job involves a number technical problems to solve and plan for, but there are numerous non-technical hurdles to jump over as well. And if you’re a brand new IT manager without a mentor or guidance, it can be hard to know how …
Best way to approach a poorly-maintained network
Windows
So this veterinary clinic is asking me to fix their computer network but they don’t know their network passwords. Users can log in but I cannot get my remote software working because they don’t know the administrator passwords to install it on them. It …
Snap! — Learning Hibernation, Sound Printing, Accidental Bomb, Waves on Mars
Spiceworks Originals
Your daily dose of tech news, in brief.
Welcome to the Snap!
Flashback: February 10, 1996: Deep Blue Defeats Kasparov (Read more HERE.)
Bonus Flashback: February 10, 2009: Satellites Collide! (Read more HERE.)
You need to hear this.
…
Spark! Pro series – 10th February 2023
Spiceworks Originals
Happy Friday!
Just a reminder, if you are reading the Spark!, Spice it
up. We like it spicy here!Today in History:
February 10, 1996 —
A Computer Defeats a World Chess ChampionWorld chess champio…
Looking for a simple app to keep notes I can access on any device.
Software
Do you keep notes that you access across multiple devices? Online on a computer or app on a mobile device? I’d like to jot things down if an idea pops into my head, when I learn something new, get a new process, discover a switch port I may need to bounce…
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Contact US
Thanks. We have received your request and will respond promptly.
Log In
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips Forums!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts - Keyword Search
- One-Click Access To Your
Favorite Forums - Automated Signatures
On Your Posts - Best Of All, It’s Free!
*Tek-Tips’s functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Students Click Here
proc sql update error expecting a setproc sql update error expecting a set(OP) 24 May 12 15:51 Hi. I am trying to update 4 columns in one table with information from 4 columns in another table. The error message i received says: ERROR 76-322: Syntax error, statement will be ignored. Here is my code, can someone help me please? PROC SQL; JOIN DWH.MEMBERS mbrs ON slmbrs.HFMemberID = mbrs.membno JOIN DWH.PROVIDER prov ON mbrs.pcpno = prov.provno SET slmbrs.pcpid = mbrs.pcpno, Red Flag SubmittedThank you for helping keep Tek-Tips Forums free from inappropriate posts. |
Join Tek-Tips® Today!
Join your peers on the Internet’s largest technical computer professional community.
It’s easy to join and it’s free.
Here’s Why Members Love Tek-Tips Forums:
- Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More…
Talk To Other MembersRegister now while it’s still free!
Already a member? Close this window and log in.
Join Us Close
IN SAS programming, we often require outputting the dataset in different formats like EXCEL and CSV etc and here are the five different ways to export the SAS dataset into .csv file.
Example:
data new ;
infile datalines dsd dlm=’ ‘ missover;
input a b c d;
datalines;
3 5 1 1
4 1 . .
5 8 3 2
6 0 4 4
;
run;
By putting MISSOVER in the infile statement we are telling SAS to do not look for the data in the next lane if it runs out of the data, instead keep missing values for any remaining variables.
DSD and DLM options should be included always in the infile statement, if we include the
dlm=’ ‘ in the infile statement then SAS will put one digit for each variable even though we haven’t assigned any length to variable.
DSD option will tell SAS to consider a missing value if 2 delimiters are present side by side in any observation.
When we ran the above program in SAS, we create a SAS dataset name ‘NEW’ in the work directory and if we want to create a .csv file of dataset ‘NEW’ here are the 5 different ways to do it:
1)Here is the simplest method and least known method to create the .CSV file of a dataset; Using the DEXPORT statement.
Here is the syntax:
dm «dexport new ‘H:abccompanyclientProgramsAnalysisproject1class.csv’ «;
When we submit the above code, SAS will automatically create a .CSV file in specified location path.
2) .CSV file can also be created using the PROC EXPORT procedure:
Here is the syntax:
proc export data=new
outfile=» H:abccompanyclientProgramsAnalysisproject1class.csv «;
run;
3) By using the ODS and the Proc print we will be able to create a .CSV file of SAS dataset.
Here is the way to do it;
ods csv file= ‘H:abccompanyclientProgramsAnalysisproject1class.csv ‘;
proc print data = new noobs;
run;
ods trace on;
ods csv close;
By keeping ODS trace on; statement we are telling SAS no to print the results in the output window, because, we are only creating the .csv file.
NOOBS option is required here, because PROC PRINT by default will create a new variable called ‘OBS’ and since we don’t require it, we should include the NOOBS option.
4) File statement can also be useful in creating the .csv file of a SAS dataset.
Here is the way to do it.
data _null_;
file » H:abccompanyclientProgramsAnalysisproject1class.csv «;
set new;
put (_all_) (‘,‘);
run;
5)b) Another way using the file statement:
filename csv ‘H:abccompanyclientProgramsAnalysisproject1class.csv‘;
data _null_;
set new ;
file csv dlm=’,‘;
put ( _all_ ) (+0);
run;
If we don’t mention the PUT( _all_), SAS will not keep all the variables in the .CSV file and even if we include put (_all_) the log will say:
ERROR 79-322: Expecting a (.
ERROR 76-322: Syntax error, statement will be ignored.
Because SAS is expecting a second parentheses here to follow the first one immediately and if we just only put the closed parenthesis to get rid of the previous error,
put (_all_) ( ) ;
We will get another error, because SAS we haven’t set any specifications for the PUT statement in the 2nd parenthesis.
So we can use a do-nothing pointer control (+0) to be error free.
Import CSV files and create SAS data sets on the fly!
Hi,
I’m new to SAS and getting an error which says the variables I’ve created (urbanrategroup, incomebracket, totalCO2) are not found. Can anyone help me understand why? The code was previously working with just two of the variables..
Much appreciated!
Here is my code:
LIBNAME mydata "/courses/d1406ae5ba27fe300" access=readonly;
DATA new;
set mydata.gapminder;
Label CO2emissions="CO2Emissions in Metric Tons"
Urbanrate="Urban Population as Percentage of Total"
incomeperperson="Income Per Person"
urbanrategroup="Rural, Moderately Urbanized, or Highly Urbanized"
incomebracket="Low, Middle, or High Income"
TotalCO2="Very Low, Low, Medium, or High";
/*set values for missing data*/
IF CO2emissions="" then Co2emissions=.;
IF Urbanrate="" then Urbanrate=.;
IF incomeperperson="" then incomeperson=.;
/*categories for urbanization rate*/
IF Urbanrate >= 0 and Urbanrate <50 then urbanrategroup="Rural";
IF Urbanrate >=50 and Urbanrate <80 then urbanrategroup="Med. Urb.";
IF Urbanrate >=80 then urbanrategroup="High Urb."
/*categories for income per person*/
IF incomeperperson >=0 and incomeperperson <=5000 then incomebracket="Low";
IF incomeperperson >5000 and incomeperperson <25000 then incomebracket="Middle";
IF incomeperperson >=25000 then incomebracket="High";
/*categories for CO2 emissions*/
IF CO2emissions <500000000 then TotalCO2="Very Low";
IF CO2emissions >=500000000 and CO2emissions <=1000000000 then TotalCO2="Low";
IF CO2emissions >1000000000 and CO2emissions <=5000000000 then TotalCO2="Medium";
IF CO2emissions >5000000000 then TotalCO2="High";
PROC SORT;
by COUNTRY;
PROC FREQ;
TABLES Co2emissions incomeperperson urbanrate urbanrategroup incomebracket TotalCO2;
RUN;
Here is my log. The error comes up on line 113, though there are a few notes before it too:
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
72
73 LIBNAME mydata "/courses/d1406ae5ba27fe300" access=readonly;
NOTE: Libref MYDATA was successfully assigned as follows:
Engine: V9
Physical Name: /courses/d1406ae5ba27fe300
74
75 DATA new;
76 set mydata.gapminder;
NOTE: Data file MYDATA.GAPMINDER.DATA is in a format that is native to another host, or the file encoding does not match the
session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce
performance.
77 Label CO2emissions="CO2Emissions in Metric Tons"
78 Urbanrate="Urban Population as Percentage of Total"
79 incomeperperson="Income Per Person"
80 urbanrategroup="Rural, Moderately Urbanized, or Highly Urbanized"
81 incomebracket="Low, Middle, or High Income"
82 TotalCO2="Very Low, Low, Medium, or High";
83
84 /*set values for missing data*/
85
86 IF CO2emissions="" then Co2emissions=.;
87 IF Urbanrate="" then Urbanrate=.;
88 IF incomeperperson="" then incomeperson=.;
89
90 /*categories for urbanization rate*/
91
92 IF Urbanrate >= 0 and Urbanrate <50 then urbanrategroup="Rural";
93 IF Urbanrate >=50 and Urbanrate <80 then urbanrategroup="Med. Urb.";
94 IF Urbanrate >=80 then urbanrategroup="High Urb."
95
96 /*categories for income per person*/
97
98 IF incomeperperson >=0 and incomeperperson <=5000 then incomebracket="Low";
__
79
ERROR 79-322: Expecting a ;.
99 IF incomeperperson >5000 and incomeperperson <25000 then incomebracket="Middle";
100 IF incomeperperson >=25000 then incomebracket="High";
101
102 /*categories for CO2 emissions*/
103
104 IF CO2emissions <500000000 then TotalCO2="Very Low";
105 IF CO2emissions >=500000000 and CO2emissions <=1000000000 then TotalCO2="Low";
106 IF CO2emissions >1000000000 and CO2emissions <=5000000000 then TotalCO2="Medium";
107 IF CO2emissions >5000000000 then TotalCO2="High";
108
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
86:17 87:14 88:20
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.NEW may be incomplete. When this step was stopped there were 0 observations and 20 variables.
WARNING: Data set WORK.NEW was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 933.53k
OS Memory 31144.00k
Timestamp 07/31/2020 03:19:59 PM
Step Count 123 Switch Count 0
Page Faults 0
Page Reclaims 160
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 8
109 PROC SORT;
110 by COUNTRY;
111
NOTE: Input data set is already sorted, no sorting done.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 538.93k
OS Memory 31144.00k
Timestamp 07/31/2020 03:19:59 PM
Step Count 124 Switch Count 0
Page Faults 0
Page Reclaims 60
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
112 PROC FREQ;
113 TABLES Co2emissions incomeperperson urbanrate urbanrategroup incomebracket TotalCO2;
ERROR: Variable URBANRATEGROUP not found.
ERROR: Variable INCOMEBRACKET not found.
ERROR: Variable TOTALCO2 not found.
114 RUN;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 470.09k
OS Memory 31144.00k
Timestamp 07/31/2020 03:19:59 PM
Step Count 125 Switch Count 0
Page Faults 0
Page Reclaims 50
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
115
116
117 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
129