Looks like you're hitting this issue: https://support.sas.com/kb/48/733.html
Does SAS continue to run and do you get the table created as the SAS Note indicates?
From the SAS Note: "Applying the hot fix prevents the SQLRC return code from being set to a non-zero value and allows the query to continue processing using a non-hash join method."
The SAS Note doesn't mention it but may-be using option magic could instruct the SQL optimizer to use a non-hash join method from start avoiding some error message being written to the log. https://support.sas.com/resources/papers/proceedings14/1240-2014.pdf
magic=102 sounds promising.
... View more
@Nilesh1603 wrote: I have started SAS base course and this is the Chapter exercise
The 2nd data step as posted can't work. Are you sure you copy/pasted this code from training material 1:1?
Is this official SAS training? And if so: Which one?
@Cynthia_sas Sorry, forgot where people get support for SAS training questions.
... View more
Here one option
data want;
do week=1 to 100;
rem=mod(week,3);
if rem = 0 then continue;
day=week+1;
llc=day+1+rem;
ulc=day+4+rem;
output;
end;
drop rem;
run;
... View more
@ccherrub I mean sample data provided via a working SAS data step and not a screenshot so we've got something to work with and test the code we propose.
Use the data step template I've provided already to provide such a SAS data step.
... View more
Depending on how you look at it - yes.
data have1;
var1=1;
do id=1,3,6;
output;
end;
run;
data have2;
var2=1;
do id=1 to 5;
output;
end;
run;
proc sql;
select t1.id, t1.var1, t2.var2
from have1 t1 left join have2 t2
on t1.id=t2.id
;
quit;
proc sql;
select t1.id, t1.var1, t2.var2
from have2 t2 right join have1 t1
on t1.id=t2.id
;
quit;
... View more
What we need is some representative sample data and desired result. This allows us to provide tested code and it also really helps to better understand what you describe.
Please provide your sample data as below:
data have;
infile datalines truncover dsd;
input Week ProductId Age TotRev Treatment HasVideo Group.;
datalines;
<and here sample data, comma separated>
;
... View more
Keep all rows from the left side as opposed to keep all rows from the right side. But if you have a left join formulated and then switch to a right join and also switch your source tables accordingly then the result will be the same.
... View more
@advanceddriver The easiest way to copy data from a database to SAS is by using one of the SAS Access engines. For this to work you need the access engine (like ODBC) licensed and have a connection to the database configured.
If that's not the case then you need the SQL - and many databases have extensions to standard SQL and though often generate code that only runs on the specific database without any changes.
If you've got the Create Table SQLs from your database then you can try and run it "as is" using SAS - but there is a good chance it's not 100% SAS SQL compliant code so you will have to figure out what needs change.
The other option is an export in json format. There you have a good chance that you can read these json's directly with SAS and that this will create the tables in SAS.
... View more
On top of what others already wrote: If you really want to clean-up your data then ideally use SAS' data quality functions (if licensed). Here an article for Viya but very similar (or even the same) code should already work in SAS9.
... View more
Piggybacking on all the code @Tom proposed here an approach with a cumulative count for days of transactions per ID (=count once per date and id).
The source data must be sorted by date but not by ID within a date - which is how transactional data is often ordered.
data have ;
input date :ddmmyy. id amount;
format date yymmdd10. ;
datalines;
01/03/2019 1 630
01/04/2019 1 90
01/05/2019 1 2
01/05/2019 1 112
01/01/2023 2 20
01/01/2023 2 100
01/01/2023 2 2
01/01/2023 2 10
01/01/2023 2 420
01/02/2023 2 98
;
data want2;
if _n_=1 then do;
/* hash for cumulative count per id and date */
declare hash h_cnt();
h_cnt.definekey('id');
h_cnt.defineData('counter');
h_cnt.definedone();
/* hash to keep track for which id already counted within a date */
declare hash h_id();
h_id.definekey('id','date');
h_id.definedone();
end;
set have;
by date;
/* not a must but keeps memory consumption lower */
if first.date then h_id.clear();
/* if new id set counter=0 else read current count from hash */
if h_cnt.find() ne 0 then counter=0;
/* if counter not increased yet for id and specific date */
if h_id.check() ne 0 then
do;
_rc=h_id.add();
counter+1;
_rc=h_cnt.replace();
end;
drop _rc;
run;
proc print data=want2;
run;
... View more
That's an example where using formats is beneficial.
@ghosh is basically proposing the same but using a table as source for the format instead of an explicit definition. I felt for your use case and assumed level of SAS expertise below syntax is likely easier to absorb.
proc format;
value GeoId2County
38001="Adams County"
38003="Barnes County"
38005="Benson County"
38007="Billings County"
38009="Bottineau County"
38011="Bowman County"
38013="Burke County"
38015="Burleigh County"
38017="Cass County"
38019="Cavalier County"
38021="Dickey County"
38023="Divide County"
38025="Dunn County"
38027="Eddy County"
38029="Emmons County"
38031="Foster County"
38033="Golden Valley County"
38035="Grand Forks County"
38037="Grant County"
38039="Griggs County"
38041="Hettinger County"
38043="Kidder County"
38045="LaMoure County"
38047="Logan County"
38049="McHenry County"
38051="McIntosh County"
38053="McKenzie County"
38055="McLean County"
38057="Mercer County"
38059="Morton County"
38061="Mountrail County"
38063="Nelson County"
38065="Oliver County"
38067="Pembina County"
38069="Pierce County"
38071="Ramsey County"
38073="Ransom County"
38075="Renville County"
38077="Richland County"
38079="Rolette County"
38081="Sargent County"
38083="Sheridan County"
38085="Sioux County"
38087="Slope County"
38089="Stark County"
38091="Steele County"
38093="Stutsman County"
38095="Towner County"
38097="Traill County"
38099="Walsh County"
38101="Ward County"
38103="Wells County"
38105="Williams County"
other=" "
;
run;
data want;
set have;
if put(geoid,GeoId2County.) = " " then delete;
run;
... View more
Based on your code it appears you don't fully grasp yet how to work with SAS Date values and all the advantages this brings.
Week numbers are unfortunately one of the trickier things to work with because there are different ways for deriving these - like does a week start on Sunday or Monday, what defines when the first week of a year starts, does the count start with 0 or with 1?
For your real data: What date range do you actually try to select? Would there be another variable in your data that we could use to define a range like:
From a given date select all rows with a date one week back and 5 weeks into the future.
Please describe in words what you have and how you want to select the data. Ideally also provide sample data representative for your real data.
If there is not other date (string) we can use than yyyyww then please explain in detail how the week number gets determined (...and there can be 53 weeks in a year).
... View more
Below article explains and provides macros for transposing that exceed what a single Proc Transpose can do. Transpose your analysis data with the %MAKELONG and %MAKEWIDE macro
The macros are available from within the article.
... View more
