BookmarkSubscribeRSS Feed
Liliya95
Fluorite | Level 6

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

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

Liliya95
Fluorite | Level 6
 
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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Liliya95
Fluorite | Level 6
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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Liliya95
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26
Liliya95
Fluorite | Level 6

Thanks so much!

Liliya95
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1141 views
  • 0 likes
  • 2 in conversation