Something like that? proc format; value agegrp 30-34 ='30-34' 35-39 ='35-39' ; run; data customer; input cust_num age_yrs gender $; datalines; 1 33 F ; data quotes; input Coverage age_grp $ female_ns female_s male_ns male_s; datalines; 100000 30-34 20.3 25.7 30.5 35.6 200000 35-39 24.6 28.4 34.6 39.1 run; proc sql; select * from customer left join quotes on put(customer.age_yrs,agegrp.)=quotes.age_grp ; quit; ... View more

Not sure if I fully understand your problem. Hopefully the following helps anyway: As you use "group by" you will get one observation for each distinct combination of orig.unit_pk,cb_cntr_service_category.service_cat_desc,orig.so_pk. The value for pk will be the minimum value per distinct combination of these 3 columns. As you select in your where close orig.unit_pk = 54 and cb_cntr_service_category.service_cat_desc = 'Video' only rows with these two values are selected and joined. (actually: I'm not sure whether the joins happen first and then the where clause subsets the result or the other way round. You might want to try and have the selections already applied during the joins in the "on.." part to improve performance). The "order by" is not necessary, "group by" sorts already. select orig.unit_pk, cb_cntr_service_category.service_cat_desc, orig.so_pk, min(sub_prod_pckg_pk) as pk from cb_subs_prod_pckg as orig left outer join cb_cntr_service_type on orig.service_type_code = cb_cntr_service_type.service_type_code left outer join cb_cntr_service_category on cb_cntr_service_category.service_cat_code = cb_cntr_service_type.service_cat_code where orig.so_pk is not null and orig.unit_pk = 54 and cb_cntr_service_category.service_cat_desc = 'Video' group by orig.unit_pk, cb_cntr_service_category.service_cat_desc, orig.so_pk "....find the first occurance of an so_pk ..." As you use "group by" you will only get one row per combination. As the only other variable is a minimum it doesn't matter whether this is "the first one" or not (the "first one" wouldn't be possible). HTH Patrick ... View more

Have a look at things like: “X command”, “X statement”, “CALL SYSTEM routine”, “%SYSEXEC” and “SYSTASK”. To use these commands within EG shell escape must be possible (-> run “proc options”, look for “xcmd”). ... View more

That's the issue: NOTE: SAS went to a new line when INPUT statement reached past the end of a line. Use TRUNCOVER. HTH Patrick data rmatrix (type = distance); infile datalines truncover; input name $ _1 _2 _3 _4 _5 _6 _7 _8 _9 _10 _11 _12 _13 _14 _15 _16 _17 _18 _19 _20 _21 _22 _23 _24 _25 _26 _27 _28 _29 _30 _34 _32 _35 _36 _37 _38 _39 _KG1 _KG2 _KG3 _KG4 _KG5 _KG6; datalines; 1 0.000 ... View more

What if: - you give us a working test dataset (valid input statement). - you give us an example showing based on the testdata showing how the desired result should look like. ... View more

What you ask for can be done but needs quite a bit of macro coding. At least I can't think of an easy way to do it. I can't help but thinking that there must be another approach for your problem. It's normally not a good idea to create a lot of datasets. Can you describe what the problem is you want to solve? Regards, Patrick ... View more

Hi Thomas "When I run the job via an %include from another job...' Does that mean you deploy the job and then in another job you're having a %include on the deployed job? If so: this wouldn't be the way you should do it in DI. Better use a loop control and embed the first job as inner object. You have to look at the code/log to see what's going wrong. One reason could be that you use the %include statement within a macro and so all macro vars created by the pre-processing step of the included job outside of a macro are "suddenly" defined inside of a macro - they would become local instead of global (and that's one of many reasons why you should do things the DI way before writing user written code). HTH Patrick ... View more

Thanks Proccontents. I was just going to write an answer before I read your post. http://groups.google.com.au/group/comp.soft-sys.sas/browse_thread/thread/382ea2336c72e44/190b2aef220ffffa?hl=en&lnk=gst&q=mahesh#190b2aef220ffffa ... View more

Hi Samuel That should do the job. HTH Patrick data have; infile datalines truncover; input var1 $1-2 var2 3-4 ; datalines; a 1 2 e 4 ; proc sql noprint; select 'sum(missing('||name||')) as '||name into :SelList2 separated by ', ' from dictionary.columns where libname='WORK' and memname='HAVE'; create table want as select &SelList2 from have; quit; title 'N Missings per Variable'; proc print data =want noobs; run; ... View more

Just a version using PROC SQL. You could also use the data set "department" to create a format (example in SAS OnlineDoc for Proc Format) and then apply this format in the Individuals table on the field "DepCode". If you need the department names for printing only this would avoid to create redundant data in a second table (using a format statement). If you still would want to create a new variable: DepartmentName=put(depcode,depfmt.); HTH Patrick data department; depcode=1; department='a';output; depcode=2; department='b';output; depcode=3; department='c';output; run; data individuals; IndCode=1;DepCode=1;output; IndCode=2;DepCode=3;output; IndCode=3;DepCode=2;output; IndCode=4;DepCode=5;output; run; proc sql noprint; create table individuals2 as select ind.*,dep.department from individuals ind left join department dep on ind.depcode=dep.depcode; quit; proc datasets lib=work nodetails nolist; delete individuals; change individuals2=individuals; quit; proc print data=individuals; run; ... View more

Do you know what datepart the timestamp part of the filename you want to read has when you run the code - or is this something you would have to determine as well (i.e. by reading the file with the most current date)? ... View more

I think the code below should do the job. Use the translate version if you want to keep as many blanks as there were 'z' - else use the compress version. HTH Patrick data name; input id$ name$ add$; array vars {*} name add; do i=1 to dim(vars); if compress(lowcase(vars{i}),'z')='' then /* vars{i}=translate(lowcase(vars{i}),' ','z');*/ vars{i}=compress(lowcase(vars{i}),'z'); put vars{i}= @; end; put; cards; mailtohim nani mainroad zzzzz zzz street raju zzAzz zzzzzzzzzzzzz zzzzzzzz zz indi run; ... View more

Hi Thomas Forget about formats when it comes to comparisons. Formats are used only for WRITING data - but calculations/comparisons are always done with the full precision. As this is user written code you can't expect DI Studio to do anything for you. And even if it wasn't user written: DI Studio does nothing else than generate SAS code - so look at the generated SAS code if you want to know what's going on. Seems as rounding the values before the UNION would be a good idea. Why not using a round() function in the select part of the SQL statement? May be that could even be implemented in a standard SQL transform (I would have to try it but I expect it to be possible). Just to show that round() works: data a; do i=1 to 5; b=i; output; end; run; data b; do i=2,3,5; b=i*1.00002; output; end; run; proc sql; select * from a union select i,round(b) from b ; quit; HTH Patrick P.S: I think it's round() you need. Have a look at the SASOnlinedoc to get the exact syntax for the precision you need (it will be something like: round(variable,0.0000001) P.P.S: The question is if the values with lower precision are rounded or truncated. You would want to do the same to the values with higher precision. If it's truncation: Use either a put/input combination or multiply by 10 power the number of digits behind the comma, use int() on the result, divide by 10 power the number of digits behind the comma. ... View more

Found something: http://support.sas.com/techsup/technote/ts654.pdf HTH Patrick ... View more

May be you use: population = sum(men,women); reason: a+b: if a or b is Missing the result will be Missing sum(a,b): Missings values are ignored, sum(a, ) resolves to value of a ... View more