topic Re: SAS code imported/exported data question in New SAS User

SAS code imported/exported data question

Jonison — Fri, 19 Jul 2019 13:11:33 GMT

Hello, I have SAS code note:

PROC SQL;
SELECT quote(MATERIAL) into: List separated by ',' FROM EMWS3.FILTER_TRAIN;
CREATE TABLE TABLELIST AS
SELECT T2.BAT_NUM,
T2.MAT_COD,
T2.UNRESTRICTED_QTY
FROM GDM_DIST.VW_MERPS_STOCKINV_INFO T2
WHERE T2.MAT_COD IN (&List);
SELECT * FROM TABLELIST;
/*INSERT INTO &EM_EXPORT_TRAIN
SELECT * FROM &TABLELIST;*/

QUIT;

I want to the TABLELIST be &EM_EXPORT_TRAIN that will be imported node input for following node. Since &EM_EXPORT_TRAIN; is created already, I tried delete and into, both methods dont work due to &EM_EXPORT_TRAIN is of 'read only'

Could you please suggest any method can put the results to &EM_EXPORT_TRAIN (exported data table of THIS SAS code Node).

Many thanks

Re: SAS code imported/exported data question

PaigeMiller — Fri, 19 Jul 2019 13:19:07 GMT

@Jonison wrote:

I want to the TABLELIST be &EM_EXPORT_TRAIN that will be imported node input for following node. Since &EM_EXPORT_TRAIN; is created already, I tried delete and into, both methods dont work due to &EM_EXPORT_TRAIN is of 'read only'

I'm really not understanding this. Please explain further.

Re: SAS code imported/exported data question

koyelghosh — Fri, 19 Jul 2019 13:39:32 GMT

@JonisonNot sure if I understood exactly what you want but you may try the code below. I have divided one SQL step into two.

PROC SQL;
	SELECT quote(MATERIAL) INTO: List separated by ',' FROM EMWS3.FILTER_TRAIN;
QUIT;

PROC SQL;
	CREATE TABLE TABLELIST AS SELECT T2.BAT_NUM, T2.MAT_COD, T2.UNRESTRICTED_QTY 
		FROM GDM_DIST.VW_MERPS_STOCKINV_INFO T2 WHERE T2.MAT_COD IN (&List);
	SELECT * FROM TABLELIST;
	/*INSERT INTO &EM_EXPORT_TRAIN
	SELECT * FROM &TABLELIST;*/
QUIT;

I am sorry if this is not what you wanted.

Re: SAS code imported/exported data question

Jonison — Fri, 19 Jul 2019 15:11:27 GMT

&EM_EXPORT_TRAIN is macro of export training dataset 'SAS Code Node'. To my understanding, in SAS miner, the export training dataset will inherit the whole import training dataset.

This 'SAS Code Node' aims selecting a sub-group of import training dataset (saved as TABLELIST), and save this to &EM_EXPORT_TRAIN which can be used as the input for the following nodes.

I tried use 'delete' function to remove the &EM_EXPORT_TRAIN table, it failed since it is read-only.

and failed too with 'update' and/or 'into' functions (update &EM_EXPORT_TRAIN content) with the same reason.

Just would like to know is there anyway to save TABLELIST content to &EM_EXPORT_TRAIN.

Thanks

Re: SAS code imported/exported data question

Jonison — Fri, 19 Jul 2019 15:19:55 GMT

Many thanks for your kind reply.

I would like to save the TABLELIST in &EM_EXPORT_TRAIN

&EM_EXPORT_TRAIN is macro of output training dataset of this node.

Re: SAS code imported/exported data question

Tom — Fri, 19 Jul 2019 15:20:38 GMT

@Jonison wrote:

&EM_EXPORT_TRAIN is macro of export training dataset 'SAS Code Node'. To my understanding, in SAS miner, the export training dataset will inherit the whole import training dataset.

This 'SAS Code Node' aims selecting a sub-group of import training dataset (saved as TABLELIST), and save this to &EM_EXPORT_TRAIN which can be used as the input for the following nodes.

I tried use 'delete' function to remove the &EM_EXPORT_TRAIN table, it failed since it is read-only.

and failed too with 'update' and/or 'into' functions (update &EM_EXPORT_TRAIN content) with the same reason.

Just would like to know is there anyway to save TABLELIST content to &EM_EXPORT_TRAIN.

Thanks

I know nothing about SAS minor. But EM_EXPORT_TRAIN is a macro variable. You are saying it contains the name of a dataset.

If you want it to point to a different data set then then change the value of the macro variable instead of trying to change the dataset that it is currently pointing to. So try something like this:

%let em_export_train=My_Other_Dataset;

Re: SAS code imported/exported data question

PaigeMiller — Fri, 19 Jul 2019 15:25:05 GMT

Can you provide the exact ERROR message you are getting?

If the problem is really that the data set is write-only, then there's really nothing in SAS that can change this and you can't overwrite or modify this data set. You need to contact your IT department, or otherwise find a way to change the permissions on this data set.

Note for clarity and proper use of terminology:

&EM_EXPORT_TRAIN is macro of export training dataset 'SAS Code Node'

&EM_EXPORT_TRAIN is not a macro. It is a macro variable. Do not confuse these two things.

Re: SAS code imported/exported data question

Jonison — Fri, 19 Jul 2019 15:42:37 GMT

Many thanks for your suggestions.
I added one code as you suggested.
========================
%let &EM_DATA_EXPORTSET=TABLELIST;
With below error message:
===
NOTE: There were 240 observations read from the data set EMWS3.FIMPORT_TRAIN.
7786 CREATE TABLE TABLELIST AS
7787 SELECT T2.BAT_NUM,
7788 T2.MAT_COD,
7789 T2.UNRESTRICTED_QTY
7790 FROM GDM_DIST.VW_MERPS_STOCKINV_INFO T2
7791 WHERE T2.MAT_COD IN (&List);
NOTE: Compressing data set WORK.TABLELIST increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Table WORK.TABLELIST created, with 133 rows and 3 columns.

7792
7793
7794 /*INSERT INTO &EM_EXPORT_TRAIN
7795 SELECT * FROM &TABLELIST;*/
7796
7797 QUIT;
NOTE: The PROCEDURE SQL printed page 4.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.19 seconds
cpu time 0.15 seconds

ERROR: Expecting a variable name after %LET.
7798 %let &EM_DATA_EXPORTSET=TABLELIST;
7799
7800 /*; *"; *'; */;

Re: SAS code imported/exported data question

Tom — Fri, 19 Jul 2019 16:52:04 GMT

ERROR: Expecting a variable name after %LET.
7798 %let &EM_DATA_EXPORTSET=TABLELIST;

You told %LET to use the value of EM_DATE_EXPORTSET as the NAME of the macro variable you wanted to create.

If you want to change the value of the macro variable EM_DATE_EXPORTSET then don't put the & in front of it in the %LET statement.

%let EM_DATA_EXPORTSET=TABLELIST;

Re: SAS code imported/exported data question

Jonison — Mon, 22 Jul 2019 11:17:37 GMT

Many thanks for your kind reply.
I was thinking to use into: to put the queried data into the table directly.
PROC SQL;
SELECT quote(MATERIAL) into: List separated by ',' FROM EMWS3.FILTER_TRAIN;
/*CREATE &EM_EXPORT_TRAIN AS*/
SELECT BAT_NUM,
MAT_COD,
UNRESTRICTED_QTY
INTO: EMWS3.EMCODE_TRAIN FROM GDM_DIST.VW_MERPS_STOCKINV_INFO
WHERE T2.MAT_COD IN (&List);
QUIT;
But system shows the error:

7946 PROC SQL;
7947 SELECT quote(MATERIAL) into: List separated by ',' FROM EMWS3.FILTER_TRAIN;
NOTE: There were 240 observations read from the data set EMWS3.FIMPORT_DATA.
NOTE: View EMWS3.FILTER_TRAIN.VIEW used (Total process time):
real time 0.12 seconds
cpu time 0.11 seconds

NOTE: There were 240 observations read from the data set EMWS3.FIMPORT_TRAIN.
7948 /*CREATE &EM_EXPORT_TRAIN AS*/
7949 SELECT BAT_NUM,
7950 MAT_COD,
7951 UNRESTRICTED_QTY
7952 INTO: EMWS3.EMCODE_TRAIN FROM GDM_DIST.VW_MERPS_STOCKINV_INFO
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: ',', -, FROM, SEPARATED, THROUGH, THRU, TRIMMED.
ERROR 76-322: Syntax error, statement will be ignored.
7953 WHERE T2.MAT_COD IN (&List);
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
7954 /*SELECT * FROM EM_EXPORT_TRAIN;*/
7955 /*%let EM_EXPORT_TRAIN=TABLELIST;*/
7956 /*%let &EMWS3.EMCODE_TRAIN=TABLELIST;*/
7957 /*INSERT INTO &EM_EXPORT_TRAIN
7958 SELECT * FROM &TABLELIST;*/
7959
7960 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: The PROCEDURE SQL printed page 11.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.13 seconds
cpu time 0.13 seconds

7961
7962 /*; *"; *'; */;

Would you please suggest on this? Many thanks