DATA Step, Macro, Functions and more

Vararray in PROC DS2

Reply
Occasional Contributor
Posts: 19

Vararray in PROC DS2

[ Edited ]

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

Super User
Super User
Posts: 9,840

Re: Vararray

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-wi...

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 databaseseems 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.

 

Occasional Contributor
Posts: 19

Re: Vararray

 
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

Super User
Super User
Posts: 9,840

Re: Vararray

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-dat... 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.

Occasional Contributor
Posts: 19

Re: Vararray

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;
Super User
Super User
Posts: 9,840

Re: Vararray

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.

Occasional Contributor
Posts: 19

Re: Vararray

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

Super User
Super User
Posts: 9,840

Re: Vararray

Occasional Contributor
Posts: 19

Re: Vararray

Thanks so much!

Occasional Contributor
Posts: 19

Re: Vararray

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

Ask a Question
Discussion stats
  • 9 replies
  • 204 views
  • 0 likes
  • 2 in conversation