goliPSU Tracker

Re: loop in SAS

goliPSU — Wed, 10 Oct 2012 19:41:37 GMT

wow, Thank you so much

Re: usin the loop

goliPSU — Wed, 10 Oct 2012 17:36:16 GMT

Thank you so much.

loop in SAS

goliPSU — Wed, 10 Oct 2012 17:35:29 GMT

This question is Not Answered.(Mark as assumed answered)

Goli Magoli Oct 10, 2012 12:59 PM

I have a SAS program that uses the micro and at the end is generating the following tables that I want to export it to SQL:

NCxC1993

NCxC1994

NCxC1995

NCxC1996

NCxC1997

I can use the following commands to create a table in SQL but I would like to use a loop:

/*proc sql;

create table sqldb.cxcuse_dense_1993 as

select * from NCxC1993;

create table sqldb.cxcuse_dense_1994 as

select * from NCxC1994;

create table sqldb.cxcuse_dense_1995 as

select * from NCxC1995;

create table sqldb.cxcuse_dense_1996 as

select * from NCxC1996;

create table sqldb.cxcuse_dense_1997 as

select * from NCxC1997;

quit;

run;*/

I am using the following loop but for some reason without any error does not create the tables in SQL (but the above command does)

%macro sqlloop(start=,end=);

%do year = &start %to &end;

create table sqldb.cxcuse_dense_&year as

select * from NCxC&year;

%end

%mend;

proc sql;

%sqlloop(start=1994,end=1995)

quit;

run;

/*-----------------------------------------------------------------------*/

quit;

run;

is there any thoughts on why the sqlloop does not work.

Thank you

usin the loop

goliPSU — Wed, 10 Oct 2012 16:59:40 GMT

I have a SAS program that uses the micro and at the end is generating the following tables that I want to export it to SQL:

NCxC1993

NCxC1994

NCxC1995

NCxC1996

NCxC1997

I can use the following commands to create a table in SQL but I would like to use a loop:

/*proc sql;

create table sqldb.cxcuse_dense_1993 as

select * from NCxC1993;

create table sqldb.cxcuse_dense_1994 as

select * from NCxC1994;

create table sqldb.cxcuse_dense_1995 as

select * from NCxC1995;

create table sqldb.cxcuse_dense_1996 as

select * from NCxC1996;

create table sqldb.cxcuse_dense_1997 as

select * from NCxC1997;

quit;

run;*/

I am using the following loop but for some reason without any error does not create the tables in SQL (but the above command does)

%macro sqlloop(start=,end=);

%do year = &start %to &end;

create table sqldb.cxcuse_dense_&year as

select * from NCxC&year;

%end

%mend;

proc sql;

%sqlloop(start=1994,end=1995)

quit;

run;

/*-----------------------------------------------------------------------*/

quit;

run;

is there any thoughts on why the sqlloop does not work.

Thank you

Re: populate vectors from a table

goliPSU — Tue, 09 Oct 2012 17:55:48 GMT

Thank you so very mcuh. It is a big help.

populate vectors from a table

goliPSU — Tue, 09 Oct 2012 16:23:54 GMT

I have a table wchich looks like below:

year elec ngas petro transp

1993 0.328962596541413 0.0333333333333333 0.0294741106255071 0.0312981997615055

1994 0.32131918710743 0.0357852882703777 0.0280891133921987 0.0296270473156796

1995 0.312689346078104 0.0301204819277108 0.0299476941480979 0.0307595385921147

1996 0.302492954358166 0.0296846011131725 0.0306720643123235 0.0315941372524655

1997 0.297555088719812 0.037109375 0.0313930047898444 0.0324508531293975

1998 0.279078628962933 0.0390455531453362 0.0324409310705472 0.0333306670536662

1999 0.269449037043657 0.0390946502057613 0.0338084853707251 0.0342346177942214

2000 0.268587236213135 0.0389105058365759 0.0354842304721889 0.0367621612481076

2001 0.267152131952974 0.0426829268292683 0.0346861443472607 0.036119401007415

2002 0.25010602338106 0.041501976284585 0.0347417788989821 0.0361976486955247

2003 0.249007386555198 0.04 0.034782939573557 0.0362781339184914

I want to automatically generate the following vectors:

hhd_nrg_coeff_order = {'elec', 'ngas', 'petro', 'transp'};

hhd_nrg_coeffs_93 = {0.328962596541413 , 0.0333333333333333, 0.0294741106255071 , 0.0312981997615055};

hhd_nrg_coeffs_94 = {0.32131918710743 , 0.0357852882703777, 0.0280891133921987, 0.0296270473156796};

....

hhd_nrg_coeffs_03 = {0.249007386555198 0.04 0.034782939573557 0.0362781339184914}

is there any way to generate it automatically in SAS.

because I have more data till 2010 so it is gana be nasty in the code.

Thanks

ANother Error

goliPSU — Thu, 04 Oct 2012 23:36:59 GMT

I have the FV_sectors (It has values as I have printed it), and the data in CONVENTION has icom and ccom where in some places I have icom which is including numbers in FV_sectors. I do not want to include them in SECTOR_LIST. but it is giving me the following error:

print FV_sectors;

use CONVENTIONAL;

/* list of all commodities */

read all var{cCom iCom} into SECTOR_LIST where ((iCom ^= FV_sectors)&(cCom ^= FV_sectors));

num_sectors = sqrt(nrow(SECTOR_LIST));

error:

431 print FV_sectors;

432 use CONVENTIONAL;

433

434 /* list of all commodities */

435 read all var{cCom iCom} into SECTOR_LIST where ((iCom ^= FV_sectors)&(cCom ^=

435! FV_sectors));

ERROR: Type mismatch in WHERE clause for variable iCom.

statement : READ at line 435 column 5

436 num_sectors = sqrt(nrow(SECTOR_LIST));

Thank you

Re: Error Message

goliPSU — Thu, 04 Oct 2012 13:43:17 GMT

Thank you.

Re: Error Message

goliPSU — Thu, 04 Oct 2012 13:23:53 GMT

Actually Yr is defined earlier:

proc iml;

%let Yr = '93';

*%include 'sector_codes Nov 25 08.sas';

%include 'C:\Users\hze1\Desktop\SAS\FoodChain\sector_codes.sas';

%macro addmargins(fob,truck,rail,water,air,pipe,whole,ret,filter=(1:num_sectors));

selector = J(1,num_sectors,0);

selector[&filter] = 1;

Yplus = diag(selector) * &fob;

/* transportation margins */

Yplus[truck_row,] = Yplus[truck_row,] + selector * &truck;

Yplus[air_row,] = Yplus[air_row,] + selector * &air;

Yplus[water_row,] = Yplus[water_row,] + selector * &water;

Yplus[rail_row,] = Yplus[rail_row,] + selector * &rail;

Yplus[pipe_row,] = Yplus[pipe_row,] + selector * &pipe;

/* wholesale & retail utility and non-utility expenditure */

Yplus[tgas_row,] = Yplus[tgas_row,] +

selector * (diag(fuel_w) * &whole + diag(fuel_r) * &ret);

Yplus[telec_row,] = Yplus[telec_row,] +

selector * (diag(elec_w) * &whole + diag(elec_r) * &ret);

Yplus[whole_row,] = Yplus[whole_row,] +

selector * ((I(num_sectors) - diag(elec_w + fuel_w)) * &whole);

Yplus[retail_row,] = Yplus[retail_row,] +

selector * ((I(num_sectors) - diag(elec_r + fuel_r)) * &ret);

%mend;

/* GET DATA */

use UTIL_RETAIL;

read all var{elec_r_&Yr} into elec_r;

read all var{fuel_r_&Yr} into fuel_r;

Error Message

goliPSU — Wed, 03 Oct 2012 20:46:26 GMT

I am receiving the following error message:

1610 read all var{fuel_r_&Yr} into fuel_r;

ERROR: FUEL_R_ is not in the scope of variables for the data set.

statement : READ at line 1610 column 5

for running the following command:

use UTIL_RETAIL;

	read all var{elec_r_&Yr} into elec_r;
	read all var{fuel_r_&Yr} into fuel_r;

any comments?

Re: Error mesasge

goliPSU — Thu, 09 Aug 2012 15:18:37 GMT

using the SASHELP library it does work without any error! but using my own data still giving this error:-(

Error mesasge

goliPSU — Thu, 09 Aug 2012 14:53:05 GMT

Hi,

I am getting this error message in IML when I am ready to use the data:

data UTIL_WHOLESALE;

set sqldb3.utilshare_wholesale_02;

proc sort; by cCom;

run;

proc iml;

/* GET DATA */

use UTIL_WHOLESALE;

read all var{elec_w} into elec_w;

read all var{fuel_w} into fuel_w;

Error is in using the UTIL_WHOLESALE;

1199 /* GET DATA */

1200

1201 use UTIL_WHOLESALE;

---

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

Any thoughts!

Re: How to use a do loop here!

goliPSU — Tue, 17 Jul 2012 16:46:46 GMT

Howles,

I believe it has been posted in different discussion groups as Rick recommended.

Sorry for any inconvenience,

Re: How to use a do loop here!

goliPSU — Tue, 17 Jul 2012 15:36:45 GMT

Hi Ballardw,

Thank you so much for your response,

I get the following error:

11465 run;

11466

11467

11468

11469 %macro sqlloop(start=,end=);

11470

11471 %do year = &start %to &end;

11472

11473 create table sqldb.CxC_&year as

11474

11475 select * from CxC&year;

11476

11477 /* create table sqldb.AValuAdded_&year as

11478

11479 select * from ValuAdded&year;

11480

11481 create table sqldb.AFinalDemand_&year as

11482

11483 select * from FinalDemand&year;*/

11484

11485

11486

11487 %mend;

ERROR: There were 1 unclosed %DO statements. The macro SQLLOOP will not be compiled.

11488

11489

11490

11491 proc sql;

11492

11493 %sqlloop(start=1993,end=1995)

180

WARNING: Apparent invocation of macro SQLLOOP not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

11494

11495 quit;

Could not display help because connection to the remote browser failed.

Re: How to loop this proc SQL

goliPSU — Tue, 17 Jul 2012 14:03:44 GMT

Thank you all, it was very helpful.

How to loop this proc SQL

goliPSU — Mon, 16 Jul 2012 20:29:58 GMT

I have the following Proc Sql to create sql tables, Instead of reapeating the commands I would like to know if I can use DO loop in proc SQL (all data in different years are available in the library)

proc sql;

create table sqldb.ACxC_1993 as

select * from CxC1993;

create table sqldb.AValuAdded_1993 as

select * from ValuAdded1993;

create table sqldb.AFinalDemand_1993 as

select * from FinalDemand1993;

create table sqldb.ACxC_1994 as

select * from CxC1994;

create table sqldb.AValuAdded_1994 as

select * from ValuAdded1994;

create table sqldb.AFinalDemand_1994 as

select * from FinalDemand1994;

create table sqldb.ACxC_1995 as

select * from CxC1995;

create table sqldb.AValuAdded_1995 as

select * from ValuAdded1995;

create table sqldb.AFinalDemand_1995 as

select * from FinalDemand1995;

quit;

run;

Thank you

How to use a do loop here!

goliPSU — Mon, 16 Jul 2012 20:21:57 GMT

I have the following Proc Sql to create sql tables, Instead of reapeating the commands I would like to know if I can use DO loop in proc SQL

proc sql;

create table sqldb.ACxC_1993 as

select * from CxC1993;

create table sqldb.AValuAdded_1993 as

select * from ValuAdded1993;

create table sqldb.AFinalDemand_1993 as

select * from FinalDemand1993;

create table sqldb.ACxC_1994 as

select * from CxC1994;

create table sqldb.AValuAdded_1994 as

select * from ValuAdded1994;

create table sqldb.AFinalDemand_1994 as

select * from FinalDemand1994;

create table sqldb.ACxC_1995 as

select * from CxC1995;

create table sqldb.AValuAdded_1995 as

select * from ValuAdded1995;

create table sqldb.AFinalDemand_1995 as

select * from FinalDemand1995;

quit;

run;

Thank you

Repating a vector in a vector form

goliPSU — Mon, 16 Jul 2012 18:13:08 GMT

Hi,

If I have a vector

v = [i

I want to repeat a vector in a vector format couple of times:

v=[i

]

Actually I would like to use this in reshaping a matrix to a vector. but I want to add a column by the variable name as the first two columns:

z = [

1 2 3

4 5 6

7 8 9]

rowname = [ a b c]

calname = [i j k]

I am using Z = shape(Z, ncol*nrow, 1);

I want to create a table looks like:

a i 1

a j 2

a k 3

b i 4

b j 5

b k 6

c i 7

c j 8

c k 9

Thank you

Re: write the SAS results into a SQL Server table

goliPSU — Tue, 10 Jul 2012 16:54:59 GMT

Thank you so much.

write the SAS results into a SQL Server table

goliPSU — Tue, 10 Jul 2012 16:20:25 GMT

Hi,

I would like to know if anyone knows how to export data from SAS to SQL Server?

Thank you.