Question : Loops in Creating Tables

[agodba]

Hi all,

I need to create a new table and my codes are below.

The thing is whenever i have to use this codes the nlig's numbers have to be changed. So i open a new excel and create a table which includes the number interval i need. After that i copy&paste it into codes.

*I tried to do do&loops but i couldn't have figured how to use it in create table codes so here i am asking for help. As a new user in SAS an answer would be appreciated.

_____________________________________________________________________________________

The codes that i need help with:

proc sql;

create table _x_1 as

select a.*,

100*a.p/b.p as wd,

100*a.p1/b.p as WD,

100*a.p2/b.p as WD_P

from lib2._a1_ a, lib2._a2_ b

where b.nlig =164 and a.nlig in (164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305)  and a.mdb=b.mdb and a.per=b.per ;

_____________________________________________________________________________________

Do&Loops i tried like these:

proc sql;

create table _x_1 as

select a.*,

%DO xx = 164 %TO 305;

if b.nlig =3 and a.nlig = xx and a.mbd=b.mbd and a.period=b.per  then;

100*a.p/b.p as wd,

100*a.p1/b.p as WD,

100*a.p2/b.p as WD_P

from lib2._a1_ a, lib2._a2_ b;

%end;

Regards,

[RW9]

Hi,

Perhaps clarify, with test data, and required output, what you are attempting to do.  Looking at your list is looks like you want;

Pre-step:

proc sql;

     select max(A.NLIG)

     into     :M_VAL

     from    HAVE;

quit;

Then use that as:

where B.NLIG=64 and (164 <= A.NLIG <= &M_VAL.)...

I.e. find you last value you want and then use that as the list looks sequential.

[agodba]

Thanks for the answer. I will try it. What if i have to create 2 or more table to do the maths. To be more clear:

Lets say i have like 1000 lines, and i have to divide the numbers in to 2 or more for calculation.

proc sql;

create table _x_0 as

select a.*,

100*a.p/b.p as wd,

100*a.p1/b.p as WD,

100*a.p2/b.p as WD_P

from lib2._a1_ a, lib2._a2_ b

where b.nlig =1 and a.nlig in (1 2 3 4 5 6 7 8 9 ........ 163)  and a.mdb=b.mdb and a.per=b.per ;

proc sql;

create table _x_1 as

select a.*,

100*a.p/b.p as wd,

100*a.p1/b.p as WD,

100*a.p2/b.p as WD_P

from lib2._a1_ a, lib2._a2_ b

where b.nlig =164 and a.nlig in (164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305)  and a.mdb=b.mdb and a.per=b.per ;

Even i have this issue rarely, i should have asked the question like this in the first place.

[agodba]

proc sql;

create table _x_0 as

select a.*,

100*a.p/b.p as wd,

100*a.p1/b.p as WD,

100*a.p2/b.p as WD_P

from lib2._a1_ a, lib2._a2_ b

where b.nlig =1 and (1<=a.nlig<=163)  and a.mdb=b.mdb and a.per=b.per ;

I couldnt have the chance to try it yet but as far as i understood the code you helped me with gave me an idea.If this code works its fine with me.

[RW9 · Accepted solution]

Well, yes.  But your probably best off doing:

proc sql;

     create table INTER as

     select     A.*,

                    100 * A.P / B.P as WD,

                    ...

     from       LIB2._A1_ A,

                   LIB2._A2_B

     where     A.MDB=B.MDB

          and     A.PER=B.PER;

quit;

data _x_0 _x_1;

     set inter;

     if nlig <= 163 then output _x_0;

     else output _x_1;

run;

I.e. do your calculations on all the data, then split it out.  To be honest though, you are probably better off keeping your data together, unless there is some specific reason (i.e. you may want to output records 1-65000 to sheet 1 of an excel file), otherwise you are better off using groupings and such like that SAS offers as will be faster.

[agodba]

Thats a perfect suggestions i would do that, but everytime i ask a question and get an answer another question mark appears in my mind.

What if that lines not in an order but in a mathematical order.

Example :

Line numbers i need to calculate with are like 5, 10, 15, 20 and so on..

How those problems are being solved?

[RW9]

There are some mathematical functions you can use to do that type of thing.  For instance in your example, if you could use mod() function:

and (mod(your_number,5)=0)

So divide by 5 and if there is nothing left over you know its a multiple of 5.