Ask me about DS2, SAS and Hadoop, Data step, SQL/FedSQL, Macro, efficiency, SAS/Access, reading data from FTP/HTTP sources, ODS and using SAS to leverage REST APIs
It's probable that, when you imported the data from dBase, PROC IMPORT assigned a permanent format of $5. to the NAME variable. If that is true, this code:
data have;
length Name $ 30;
set have;
if _n_ eq 1 then Name="Rumpelstiltskin";
run;
actually DID change the length of the variable, but it was only displaying 5 characters by default, because of the format. Subsequently running this code:
data have;
format Name $30.;
set have;
if _n_ eq 1 then Name="Rumpelstiltskin";
run;
would replace the $5. format with $30. and allow the extra characters to display. This PROC SQL solution simultaneously modifies both the length of the variable and the format:
proc sql;
alter table lib.have
modify TextVar char(20) format=$20.;
quit;
Alternatively, you could use both LENGTH and FORMAT statements in the DATA step code to accomplish the same result:
data have;
length Name $30;
format Name $30.;
set have;
if _n_ eq 1 then Name="Rumpelstiltskin";
run;
... View more
This is a perfect opportunity to leverage some PROC SQL 🙂 Assuming your imported data set is "lib.have" and the name of the character variable you want to modify is "TextVar", this should work for you:
proc sql;
alter table lib.have
modify TextVar char(20) format=$20. informat=$20.;
quit;
... View more
Scott, Take the quotations out of your macro code and it will work fine: * pure macro equivalent? ;
%let today=%sysfunc(today());
%let friday=%sysfunc(intnx(week1.6,(&today-1),0));
%let eom=%sysfunc(intnx(month,&today,-1,e));
%let max=%sysfunc(max(&friday,&eom));
%let where=clmDt < &max;
%put &where;
... View more
Try this:
Data temp;
infile datalines dlm="|" truncover dsd;
input _b:$20. _r:$20.;
if _infile_='|' then output;
else do _i=1 to 2;
bcode=scan(_b,_i,',');
if not missing(bcode) then output;
call missing(bcode);
rcode=scan(_r,_i,',');
if not missing(rcode) then output;
call missing(rcode);
end;
drop _:;
datalines;
tcs,satyam|123,456
|
rnrl|567
run;
... View more
Oleg, I've written a workshop for SAS Users Groups called "Untangling the World Wide Web" You can download a ZIP file containing the PDF and sample SAS code at http://goo.gl/oinmj
... View more
Try this:
data ID_REDACTED;
infile datalines truncover;
input text1 $100.;
text2 = prxchange('s/\s\w1\d+\s/ *REDACTED* /', -1, text1);
datalines;
This patent ID abcdef01 should be removed.
This is NOT a patent ID abcdefgh and should remain.
This is NOT a patent ID 123455 and should remain.
;
run;
Message was edited by: Mark Jordan to improve the Regular Expression
... View more
If you provide a sample of the output you are trying to produce, I could probably provide more specific advice. But perhaps applying the QTR. or YYQ. formats to your date could help?
... View more
If your data is large, it could be slightly more efficient to execute only one function. You could accomplish the task something like this: /*Make some bigger data to play with*/
DATA Transactions(SORTEDBY=customer);
/* Customer one has only one transaction */
Customer=1;
Product_type='B';
output;
do Customer=2 to 10000;
/* Customers 2-10000 may have several transactions */
do i=1 to 100;
select (round(RANUNI(0)*4));
when (1) Product_type='A';
when (2) Product_type='B';
when (3) Product_type='C';
when (4) Product_type='D';
otherwise Product_type='';
end;
/* Customers 100,1000 and 10000 will only buy product B */
if Customer in (100,1000,10000) then Product_type='B';
if Product_type ne '' then output;
end;
end;
RUN;
/* Get the unique ID for customers who bought only product_type B */
/* Should include customers 1, 100, 1000 and 10000 */
PROC SQL;
CREATE TABLE B_Customers as
SELECT customer
FROM Transactions a
group by customer
having sum(product_type <> 'B')=0
;
QUIT; Select distinct is not required in this case.
... View more
You can capture the number of elements in a macro variable and use that value to set the new array dimension: /* Make some data to play with */
data cus1;
array Visit{15};
do obs=1 to 100;
do i=1 to dim(Visit);
Visit{i}=ranuni(1);
if Visit{i} > .8 then Visit{i}=.;
end;
Misses=NMISS(of Visit{*});
output;
end;
drop obs i;
run;
/* Find out how many elements, store in macro variable Num */
data _null_;
set cus1;
array V{*} visit:;
call symputx('Num',dim(V));
stop;
run;
%PUT NOTE: There were &Num elements in the V array;
/* Now create the new array using &Num as the array size */
data cus2;
set cus1;
array V{*} visit:;
array Visit_{&Num} ;
do i = 1 to dim(V);
Visit_{i} = V{i} + 1;
end;
run;
Or, you could query the dictionary tables to find out how many varibles names start with 'visit' in the cus1 data set: proc sql noprint;
select count(*)
into :Num
from dictionary.columns
where LIBNAME ='WORK'
and MEMNAME='CUS1'
and lowcase (Name) like 'visit%'
;
quit;
... View more
The resource hog is the correlated subquery. Try to do it with an non-correlated subquery instead. For example, if FriedEgg's data sets are a good simulation, try this SQL query instead: proc sql stimer;
create table del as
select distinct r.customer_ID
from nbcase as nb
, Recover as r
where r.customer_id=nb.customer_id
and ( r.nb_created_date < nb.nb_first_date
or r.nb_created_date > nb.nb_last_date )
;
delete from recover r
where Customer_ID in (select Customer_ID
from del)
;
quit;
Even though I created a temp table, the 2 queries above consumed a total of 0.8 CPU seconds vice 16.5 CPU seconds. Elapsed time was 0.3 seconds vice 16 seconds for the original query (I have a quad core processor, so CPU time sometimes exceeds Elapsed Time when multi-threading). With the constant re-execution of the correlated subquery (inner query ) in the original code, the process is probably I/O bound. The non-correlated sub query in the code above (inner query) executes once and passes the resulting list of values to the outer query. This is much less I/O intensive. YMMV but it's worth a shot. Message was edited by: Mark Jordan due to spelling errors in the original entry.
... View more
I did a test using SAS9.2 on Wndows 7: Contents of test.bat file: echo Parameter was %1; SAS Code: %sysexec "c:\temp\test.bat Testing > c:\temp\result.txt "; result.txt file contents after run: C:\Users\SASJedi>echo Parameter was Testing; Parameter was Testing; Conclusion: Parameter is passing OK to a normal DOS batch file Upon reviewing the documentation for PSFTP, I found instructions for writing scripts, but none for accepting parameters. Perhaps this is problems stems from the PSFTP program itself. Consider re-writing your job as a DOS batch file (which will accept parameters) and let the DOS batch file execute PSFTP, or use a DATA _NULL_ step to write a customized copy of the extract_market_files.bat file which contains the hard-coded local directory name so it doesn't have to be passed as a parameter.
... View more
Your SAS code looks OK, but many things can affect using SAS X commands in a client-server environment. The first step is to be able to see what the problem is with the script execution. Try piping the results of the script execution to a file, something like this: options XSYNC;
data _NULL_;
x "cscript D:\Reports\vbs\report.vbs > D:\Reports\vbs\report_log.txt";
run;
Review the contents of D:\Reports\vbs\report_log.txt after your code executes for clues as to what went wrong.
... View more
This should do it for you: proc sql;
create table report as
select c.customer_id
, coalesce(Count,0) as visit_cnt
from customer c
left join
(SELECT customer_id, count(*) as Count
from visit
where visit_date between '01apr2011'd and '30apr2011'd
group by 1) v
on c.customer_id=v.customer_id
;
quit;
... View more
And just to round out a complete anwer to the original question, an XML file is just a specialy structured text file, so yes - you could read it in a DATA step using INFILE and INPUT statements. Some PERL regular expressions and the SAS PRX functions would facilitate parsing your data from amidst the XML tags. But that sure would be a lot of extra work! As Cynthia and Art pointed out, the SAS XML Mapper is free, automatically generates the XML map file and even provides sample SAS code for accessing the XML data. With LIBNAME access, reading XML data is just as easy as reading a regular SAS data set. Mark
... View more
