BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
agodba
Calcite | Level 5

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,

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Calcite | Level 5

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. Smiley Happy

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 916 views
  • 3 likes
  • 2 in conversation