About Q1983

Q1983 · ‎04-19-2022

Lets say I have a libname called myfiles with the contents as follows: acct_cd_20210401 acct_cd_20210412 cof_fod_20200419 cof_fod_20220411 mrt_ftr_20200402 mrt_ftr_20220418 I want to do a proc contents on the last dataset in each category by name and date only (ie acct_cd_20210412,cof_fod_20220411,mrt_ftr_20220418) proc contents data=myfiles. ???????? I want to get the names of fields in the tables however I do not want to have to do a proc contents on each and every table name. Can such a query be done?

Q1983 · ‎04-06-2022

data test; last_name = 'stanley jr.'; run; data _null_; file "my_file.txt" lrecl=119 TERMSTR=crlf; set test; put @1 last_name $26. ;run; The customer receiving the file runs it through an outside program For some reason it gives an invaild character message. It appears the period at the end of Jr is causing the invalid character. Is there a way to truncate the period at the end of the name if it exists to help avoid the invalid character

Q1983 · ‎03-05-2022

I have a date from a dataset called delivery_date. It defaults to 01jan1900. I inserted a datepart(delivery_date) format=mmddyy10.; It then defaults to 01/01/0000 It will not provided the correct date from the dataset.

Q1983 · ‎02-27-2022

The split based on Make is needed because I need to report to different entities (customers) based on Make. Actually I am just using the sashelp.cars as an example. The true split is based on csv files inputted from different states and processed through a sas program The results need to be split based on the state.

Q1983 · ‎02-26-2022

%Let rptout = /myshare; data have; set sashelp.cars; run; %macro eof (Make); Data _Null_; Set have; FILE "&rptout/&Make._report1.csv" DLM="," dsd; RUN; %mend; %eof(Acura,'Acura'); %eof(Audi,'Audi'); %eof(BMW,'BMW'); 1. Is there a way to automate programmatically and segment into separate datasets by make? (ie if make = 'Acura' output Acura) 2. After executing step 1 is there a way to proc export based on the number of datasets created. Objective is to assign the Make as the name of each dataset at export

Q1983 · ‎02-19-2022

Thanks the solution worked. Here is my question. Using that same script you offered Is there a way to use an excel file extension (ie xlsb or xlsx) using those same headers? proc sql noprint; select quote(strip(name)),varnum into :header3 separated by " ',' ", :dummy from dictionary.columns where (memname = 'HAVE' and libname = 'WORK') order varnum; quit; %put NOTE: &=header3; %let header1 = %str(7,0,,,,,Data Needed ,,,New Submission data,,,,,,,,,); %let header2 = %str(Status,,,,,Data Needed ,,,,,New Submission data,,,,,,,,,); %put &header1; %put &header2; Data _Null_; Set have ; FILE "./Have.csv" dsd; IF _N_ EQ 1 THEN DO; PUT "&Header1"; PUT "&Header2"; put &header3; *HEADEROUT = 1; END; put (_all_)(:); RUN; I tried using xlsb and xlsx and it generates a file extension error "cannot open"

Q1983 · ‎02-18-2022

data _null_; call symput('tday',put(today(),mmddyy8.)); run; %put &tday ; data test; my_date = INTNX('MONTH',&tday,1,'SAMEDAY') ; format my_date mmddyy8.; run; I want today's date exactly one month from now. Code runs without issue but is producing 2/1/60 (today is 2/18/22) I want in this case to show 3/18/22

Q1983 · ‎02-12-2022

You would be unable to run this code. I am inputting a csv file name AL_MAA_mystate_FromState_20220211_02.csv. I defined a numeric variable called code. It was designed to choose the appropriate number at the end of the _ (ie _01 or _02) as follows: %let CODE = %str(01,02,03); %If &filehere=Y %Then %DO; Data &ThisStateID._Data; infile "&myPath/&ThisStateID./&ThisStateRpt.&CURRDATE_KEY._&CODE..csv" LRECL=32767 FIRSTOBS=4 ENCODING="LATIN1" DLM=',' MISSOVER DSD ; &INPUTLIST; Run; %End; Instead of getting the correct number extension I get an error message showing all 3 entries as follows ERROR: Physical file does not exist, /myshare/mydrive0/al/AL_MAA_mystate_FromState_20220211_01,02,03.csv. Is there a way to get the code to select the correct numeric extension instead. In this case it would be _02

Q1983 · ‎01-29-2022

Cancel the question I believe I have solution. Thanks

Q1983 · ‎01-29-2022

Thanks this worked. I should have specified that the third row represents the column headers beginning with Customer, Revenue etc. I also want to show the column name underneath the two column header from the code you showed me. How can I do that portion???

Q1983 · ‎01-28-2022

Thanks this worked. I should have specified that the third row represents the column headers beginning with Customer, Revenue etc. I also want to show the column name underneath the two column header from the code you showed me. How can I do that portion???

Q1983 · ‎01-28-2022

data have; set sashelp.electric; run; %let header1 = %str(7,0,,,,,Data Needed ,,,New Submission data,,,,,,,,,); %let header2 = %str(Status,,,,,Data Needed ,,,,,New Submission data,,,,,,,,,); %put &header1; %put &header2; Data _Null_; Set have ; FILE "C:/Have..csv" LINESIZE=8000 DLM="," dsd; IF _N_ EQ 1 THEN DO; PUT "&Header1"; PUT "&Header2"; HEADEROUT = 1; END; RUN; I am trying to insert a 2 row header on a csv file and then export it. It needs to be spaced and should look something like this. Is there a way to do this with a %let statement for the header as shown in my code 7 0 Data Needed New Submission Data Status Data Needed New Submission Data Customer Revenue Year RevTip AllPower AllTip Coal CoalTip Nuclear NukeTip NaturalGas GasTip Hydro HydroTip Other OtherTip Commercial $63 1994 title="Bar: Commercial Revenue: $63 (billion)" Industrial $48 1994 title="Bar: Industrial Revenue: $48 (billion)" Other $7 1994 title="Bar: Other Revenue: $7 (billion)" Residential $85 1994 title="Bar: Residential Revenue: $85 (billion)" 3,247,522 title="Line: All sources Year: 1994 Power: 3,247,522 GWh" 1,690,694 title="Line: Coal Year: 1994 Power: 1,690,694 GWh" 640,440 title="Line: Nuclear Year: 1994 Power: 640,440 GWh" 460,219 title="Line: Natural Gas Year: 1994 Power: 460,219 GWh" 260,126 title="Line: Hydropower Year: 1994 Power: 260,126 GWh" 196044 title="Line: Other sources Year: 1994 Power: 196,044 GWh" Commercial $66 1995 title="Bar: Commercial Revenue: $66 (billion)" Industrial $47 1995 title="Bar: Industrial Revenue: $47 (billion)" Other $7 1995 title="Bar: Other Revenue: $7 (billion)" Residential $88 1995 title="Bar: Residential Revenue: $88 (billion)" 3,353,487 title="Line: All sources Year: 1995 Power: 3,353,487 GWh" 1,709,426 title="Line: Coal Year: 1995 Power: 1,709,426 GWh" 673,402 title="Line: Nuclear Year: 1995 Power: 673,402 GWh" 496,058 title="Line: Natural Gas Year: 1995 Power: 496,058 GWh" 310,833 title="Line: Hydropower Year: 1995 Power: 310,833 GWh" 163768 title="Line: Other sources Year: 1995 Power: 163,768 GWh" Commercial $68 1996 title="Bar: Commercial Revenue: $68 (billion)" Industrial $48 1996 title="Bar: Industrial Revenue: $48 (billion)" Other $7 1996 title="Bar: Other Revenue: $7 (billion)" Residential $91 1996 title="Bar: Residential Revenue: $91 (billion)" 3,444,188 title="Line: All sources Year: 1996 Power: 3,444,188 GWh" 1,795,196 title="Line: Coal Year: 1996 Power: 1,795,196 GWh" 674,729 title="Line: Nuclear Year: 1996 Power: 674,729 GWh" 455,056 title="Line: Natural Gas Year: 1996 Power: 455,056 GWh" 347,162 title="Line: Hydropower Year: 1996 Power: 347,162 GWh" 172046 title="Line: Other sources Year: 1996 Power: 172,046 GWh" Commercial $70 1997 title="Bar: Commercial Revenue: $70 (billion)" Industrial $47 1997 title="Bar: Industrial Revenue: $47 (billion)" Other $7 1997 title="Bar: Other Revenue: $7 (billion)" Residential $91 1997 title="Bar: Residential Revenue: $91 (billion)" 3,492,172 title="Line: All sources Year: 1997 Power: 3,492,172 GWh" 1,845,016 title="Line: Coal Year: 1997 Power: 1,845,016 GWh" 628,644 title="Line: Nuclear Year: 1997 Power: 628,644 GWh" 479,399 title="Line: Natural Gas Year: 1997 Power: 479,399 GWh" 356,453 title="Line: Hydropower Year: 1997 Power: 356,453 GWh" 182661 title="Line: Other sources Year: 1997 Power: 182,661 GWh" Commercial $73 1998 title="Bar: Commercial Revenue: $73 (billion)" Industrial $47 1998 title="Bar: Industrial Revenue: $47 (billion)" Other $7 1998 title="Bar: Other Revenue: $7 (billion)" Residential $93 1998 title="Bar: Residential Revenue: $93 (billion)" 3,620,295 title="Line: All sources Year: 1998 Power: 3,620,295 GWh" 1,873,516 title="Line: Coal Year: 1998 Power: 1,873,516 GWh" 673,702 title="Line: Nuclear Year: 1998 Power: 673,702 GWh" 531,257 title="Line: Natural Gas Year: 1998 Power: 531,257 GWh" 323,336 title="Line: Hydropower Year: 1998 Power: 323,336 GWh" 218484 title="Line: Other sources Year: 1998 Power: 218,484 GWh" Commercial $73 1999 title="Bar: Commercial Revenue: $73 (billion)" Industrial $47 1999 title="Bar: Industrial Revenue: $47 (billion)" Other $7 1999 title="Bar: Other Revenue: $7 (billion)" Residential $93 1999 title="Bar: Residential Revenue: $93 (billion)" 3,694,810 title="Line: All sources Year: 1999 Power: 3,694,810 GWh" 1,881,087 title="Line: Coal Year: 1999 Power: 1,881,087 GWh" 728,254 title="Line: Nuclear Year: 1999 Power: 728,254 GWh" 556,396 title="Line: Natural Gas Year: 1999 Power: 556,396 GWh" 319,536 title="Line: Hydropower Year: 1999 Power: 319,536 GWh" 209537 title="Line: Other sources Year: 1999 Power: 209,537 GWh" Commercial $78 2000 title="Bar: Commercial Revenue: $78 (billion)" Industrial $49 2000 title="Bar: Industrial Revenue: $49 (billion)" Other $7 2000 title="Bar: Other Revenue: $7 (billion)" Residential $98 2000 title="Bar: Residential Revenue: $98 (billion)" 3,802,105 title="Line: All sources Year: 2000 Power: 3,802,105 GWh" 1,966,265 title="Line: Coal Year: 2000 Power: 1,966,265 GWh" 753,893 title="Line: Nuclear Year: 2000 Power: 753,893 GWh" 601,038 title="Line: Natural Gas Year: 2000 Power: 601,038 GWh" 275,573 title="Line: Hydropower Year: 2000 Power: 275,573 GWh" 205337 title="Line: Other sources Year: 2000 Power: 205,337 GWh" Commercial $86 2001 title="Bar: Commercial Revenue: $86 (billion)" Industrial $50 2001 title="Bar: Industrial Revenue: $50 (billion)" Other $8 2001 title="Bar: Other Revenue: $8 (billion)" Residential $103 2001 title="Bar: Residential Revenue: $103 (billion)" 3,736,644 title="Line: All sources Year: 2001 Power: 3,736,644 GWh" 1,903,956 title="Line: Coal Year: 2001 Power: 1,903,956 GWh" 768,826 title="Line: Nuclear Year: 2001 Power: 768,826 GWh" 639,129 title="Line: Natural Gas Year: 2001 Power: 639,129 GWh" 216,961 title="Line: Hydropower Year: 2001 Power: 216,961 GWh" 207771 title="Line: Other sources Year: 2001 Power: 207,771 GWh" Commercial $87 2002 title="Bar: Commercial Revenue: $87 (billion)" Industrial $48 2002 title="Bar: Industrial Revenue: $48 (billion)" Other $7 2002 title="Bar: Other Revenue: $7 (billion)" Residential $107 2002 title="Bar: Residential Revenue: $107 (billion)" 3,858,452 title="Line: All sources Year: 2002 Power: 3,858,452 GWh" 1,933,130 title="Line: Coal Year: 2002 Power: 1,933,130 GWh" 780,064 title="Line: Nuclear Year: 2002 Power: 780,064 GWh" 691,006 title="Line: Natural Gas Year: 2002 Power: 691,006 GWh" 264,329 title="Line: Hydropower Year: 2002 Power: 264,329 GWh" 189923 title="Line: Other sources Year: 2002 Power: 189,923 GWh" Commercial $96 2003 title="Bar: Commercial Revenue: $96 (billion)" Industrial $52 2003 title="Bar: Industrial Revenue: $52 (billion)" Other $1 2003 title="Bar: Other Revenue: $1 (billion)" Residential $111 2003 title="Bar: Residential Revenue: $111 (billion)" 3,883,185 title="Line: All sources Year: 2003 Power: 3,883,185 GWh" 1,973,737 title="Line: Coal Year: 2003 Power: 1,973,737 GWh" 763,733 title="Line: Nuclear Year: 2003 Power: 763,733 GWh" 649,908 title="Line: Natural Gas Year: 2003 Power: 649,908 GWh" 275,806 title="Line: Hydropower Year: 2003 Power: 275,806 GWh" 220002 title="Line: Other sources Year: 2003 Power: 220,002 GWh" Commercial $101 2004 title="Bar: Commercial Revenue: $101 (billion)" Industrial $53 2004 title="Bar: Industrial Revenue: $53 (billion)" Other $1 2004 title="Bar: Other Revenue: $1 (billion)" Residential $116 2004 title="Bar: Residential Revenue: $116 (billion)" 3,970,555 title="Line: All sources Year: 2004 Power: 3,970,555 GWh" 1,978,620 title="Line: Coal Year: 2004 Power: 1,978,620 GWh" 788,528 title="Line: Nuclear Year: 2004 Power: 788,528 GWh" 708,979 title="Line: Natural Gas Year: 2004 Power: 708,979 GWh" 268,417 title="Line: Hydropower Year: 2004 Power: 268,417 GWh" 226011 title="Line: Other sources Year: 2004 Power: 226,011 GWh" Commercial $111 2005 title="Bar: Commercial Revenue: $111 (billion)" Industrial $58 2005 title="Bar: Industrial Revenue: $58 (billion)" Other $1 2005 title="Bar: Other Revenue: $1 (billion)" Residential $128 2005 title="Bar: Residential Revenue: $128 (billion)" 4,054,688 title="Line: All sources Year: 2005 Power: 4,054,688 GWh" 2,013,179 title="Line: Coal Year: 2005 Power: 2,013,179 GWh" 781,986 title="Line: Nuclear Year: 2005 Power: 781,986 GWh" 757,974 title="Line: Natural Gas Year: 2005 Power: 757,974 GWh" 269,587 title="Line: Hydropower Year: 2005 Power: 269,587 GWh" 231962 title="Line: Other sources Year: 2005 Power: 231,962 GWh"

Q1983 · ‎01-02-2022

%Let pth = /share/sas_tmp; data have; length dept $25 month_ 4 count_ 8 current 8 ; input dept month_ :monyy5. count_ current; format month_ yymmd7.; datalines; Atlantic JAN20 1 1 Atlantic JAN20 1 0 Central FEB20 1 1 Central APR20 1 1 Central MAR20 1 0 Pacific APR20 1 0 Pacific FEB20 1 1 ; run; proc sql; create table have2 as select dept label = 'Department', month_ label = 'Month', count_ label = 'Count', current label = 'Current' from have ;quit; proc export data=work.have2 outfile="/&pth./have3.csv" label dbms=csv; run; 1. Files successfully writes to the sharefolder location. The desire is a csv file however it saves as an excel. I dont know why 2. I want one output file however I want to separate into tabs based on the Department 3. I need a header in the first two rows so the actual data should start on the third row Desired output in my export is as follows with a separate tab based on the Department: 7 3 Data Needed Key Loan Data Current Data Department Month Count Current Atlantic 2020-01 1 1 Atlantic 2020-01 1 0 Central 2020-02 1 1 Central 2020-04 1 1 Central 2020-03 1 0 Pacific 2020-04 1 0 Pacific 2020-02 1 1

Q1983 · ‎12-17-2021

proc sql noprint; connect to DB2 (&cpplogin); create table t_escrow as select * from connection to db2 ( SELECT distinct * FROM DX.T_escrow_1 WHERE tran_date >= '2019-01-01' and loan_num in ('2211','3123','908766','9876') /*need to replace this line with a new select statement and new passthrough connection.*/ and UPDATE_CD IN ('A','C') order by ln_no,tran_date desc WITH UR ); disconnect from db2; quit; proc sql; connect to sqlsvr (&dlogin_new.); CREATE TABLE loan_lookup AS ( SELECT * FROM connection to sqlsvr ( select lnbr FROM D_Tran.dbo.DT_DBBT_Lookup ) ) ; disconnect from sqlsvr; run; quit; I need to integrate loan_lookup into the t_escrow table. It will be filtered based on the loan_lookup query however notice the connections are slightly different. Would I need to run the queries separately then combine them (not preferred) or is there a way to handle this in the same iteration despite two different connection strings

Q1983 · ‎12-15-2021

data new; comm_time = '13:08:09'; numeric_comm_time = input(comm_time, ???.); run; The actual dataset shows the comm-time as a char variable. Since I will need to use the comm_time in a calculation with a date IE if date1 < date2 and com_time1 < comm_time 2 then do something...... I will need to display/convert the char variable to a numeric variable. I just do not know what to insert in the input value where I am showing ??? Notice it shows as 13:08:09. Is there an informat to address this???

Online Status	Offline
Date Last Visited	‎05-08-2023 02:16 PM

Date will not change to Character

Date reading as 1960 after conversion

YYYMMDD where the field reads as a date

Change decimal number to a character

Suppress fields based on dataset in ods output

Proc report conditional fields

Re: Import CSV issue

Import CSV issue

Datetime format question

Date not displaying from database properly

Re: Date will not change to Character

Re: Date reading as 1960 after conversion

Re: YYYMMDD where the field reads as a date

Re: Suppress fields based on dataset in ods output

Re: Headers on first two rows of csv for export

output based on variable in dataset

Defined variable for file extension

Re: Headers on first two rows of csv for export

proc contents on last dataset by name and date

Invalid character produces error at export

Date variable defaults to 1/1/1900

Re: output based on variable in dataset

output based on variable in dataset

Re: Headers on first two rows of csv for export

Date macro wrong result

Defined variable for file extension

Re: Headers on first two rows of csv for export

Re: Headers on first two rows of csv for export

Re: Headers on first two rows of csv for export

Headers on first two rows of csv for export

Header and Segment by Variable

Passthrough DB2 and SQL in same iteration

Informat to convert char time variable to numeric time variable