topic Re: Vararray in SAS Programming

Vararray in PROC DS2

Liliya95 — Mon, 27 Nov 2017 13:55:45 GMT

How to work with arrays in ds2? There is an array in the proc step. Need to create it indefinite long, the length of the array will consist of the amount of data loaded from the database

Re: Vararray

RW9 — Mon, 27 Nov 2017 11:18:30 GMT

I am sorry, please review the how to post a question text which you find next to the post information. You have to provide some information for us to work with. You mention ds2, why/how are you using this, providing code is useful - use the code window (its the {i} above post area). You talk about varrays, which I assume you picked up from here:

https://blogs.sas.com/content/sgf/2016/04/22/solutions-for-many-of-the-missing-data-step-features-within-ds2/

These tend to need pre-known item counts, but there are ways around it. But then your statement "Need to create it indefinite long, the length of the array will consist of the amount of data loaded from the database" seems to imply that you do not know what arrays are for. They are temporary references to a group of variables - nothing to do with observations, so you know the structure of your data, that is all that is necessary to be able to create an array on the structure. Again though, you haven't provided any information that would help us understand your problem.

Re: Vararray

Liliya95 — Mon, 27 Nov 2017 13:15:51 GMT

It is necessary to create an array for storing the information that I download from the database.

proc ds2;

package qqq/overwrite=yes;

 

vararray double mothpayment[n]; <- n Number of Payments, this number is not known in advance. This means that we do not know in advance what length the array will be.


 

dcl package SQLStmt stmtCredits('SELECT count(*) FROM WORK.monthly_payment t');

 

method execute()After we execute the query, we get a number that means the size of the array mothpayment

rc= stmtCredits.execute();

if (rc = 0) then do; 

rc= stmtCredits.bindresults([count]);

rc= stmtCredits.fetch();

rc = stmtCredits.fetch();

end;

end;

so the question is how to set the array correctly or how to write the code correctly so that in the future the size of the array is equal to the number of rows received from the database

Re: Vararray

RW9 — Mon, 27 Nov 2017 13:26:00 GMT

Yes, and here is the nub of my point. Arrays deal with variables, not observations. So if your data is structured like this:

monthpayment1 monthpayment2 ... monthpaymentX

then arrays would be there to deal with with this and you can get this from the data structure. If however your database is designed by anyone with any sense, your data will be normalised, something like:

month payment

1 ...

2 ..

...

X ...

Arrays are not the way to handle this type of data, that is for aggregate functions in SQL, or datastep in SAS.

So again, I ask what does your data look like, post an example - doesn't have to be real - in the form of a datastep (you can follow: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712). This will show your data. Next question is, why are you using proc ds2? If you have the data downloaded from a database, you can use simple datastep language to process it.

Re: Vararray

Liliya95 — Mon, 27 Nov 2017 13:52:29 GMT

my table with monthly payments

data mothly_payment;/*Временная таблица платежей*/
   attrib P length = 8;
 
   infile datalines dsd;
   input
     P
   ;
datalines4;
9000
6002,
172,
1700,
1500,
800,
2800,
305,
1900,
;;;;
run;

my code

proc ds2;
package qqq/overwrite=yes; 
vararray double mothpayment[n];
dcl double Ost;
dcl int s rc rc1 i j h k n count;

dcl package SQLStmt stmtCount('SELECT count(*) FROM WORK.monthly_payment t');

dcl package SQLStmt stmtCredits('SELECT t.m_p FROM WORK.monthly_payment t order by t.m_p desc');

method execute()

Ost =11903;
minsum=0;

rc= stmtCount.execute();
if (rc = 0) then do; 
rc= stmtCount.bindresults([count]);
rc= stmtCount.fetch();
rc = stmtCount.fetch();
end;


	rc=	stmtCredits.execute(); 

				if (rc = 0) then do;
							rc=	stmtCredits.bindresults([MonthlyPaymentCC]); 
							rc=	stmtCredits.fetch();	

									i=1;put count= ; 								
										do while (i<=count);put MonthlyPaymentCC= ;
											mothpayment[i] = MonthlyPaymentCC;
											minsum=minsum+MonthlyPaymentCC;											
											rc = stmtCredits.fetch();							
											i=i+1;
										end;
										put mothpayment[*]= ;
										put minsum= ;

							
				end; 
 		
				n=1;
				do i=1 to count;
					if(mothpayment[i]>=Ost) then do;
 						minsum=mothpayment[i]; n=i; inddd=i;				
					end;
				end;
			put minsum= ;		


end;

Re: Vararray

Liliya95 — Mon, 27 Nov 2017 13:53:52 GMT

please help me figure it out. I'm just starting to learn ds2 and do not know many things

Re: Vararray

RW9 — Mon, 27 Nov 2017 13:59:54 GMT

Sorry, not sure how I can say this any other way. An array is an array of variables not observations. You have only one variable, therefore your "array" would be one data item each row - i.e. there is no array. Perhaps start by learning how to do your task in Base SAS using datasteps before jumping into ds2. To note, varray just checks if the variable is part of an array. As for using ds2, not found a use for it as yet, Base SAS does everything. Download your data to a SAS dataset and then process it using Base SAS.

Re: Vararray

Liliya95 — Mon, 27 Nov 2017 14:10:13 GMT

can you write an example of using an array on sas base please?

Re: Vararray

RW9 — Mon, 27 Nov 2017 14:15:00 GMT

Here is a good paper on the subject:

https://www.sas.com/content/dam/SAS/support/en/technical-papers/arrays1780.pdf

Re: Vararray

Liliya95 — Mon, 27 Nov 2017 14:17:22 GMT

Thanks so much!