sql and data step taking lot of time while running

aanan1417 — Mon, 21 Nov 2022 07:20:20 GMT

Hi sir,

i ran a program in sql and in data step it take 3 hours to complete .

is their any other way so that it can complete i few minute.

data sas0416.CHDR_202210;
set test1.CHDR;
run;

proc sql;
create table sas0416.CHDR_202210 as
select * from test1.CHDR;
quit;

Re: sql and data step taking lot of time while running

andreas_lds — Mon, 21 Nov 2022 07:26:19 GMT

If you want to copy a dataset, use proc copy. I don't know, of course, if the proc can reduce the runtime to minutes, because i know nothing about the environment you are working in. Please post proc contents of test1.chdr.

Re: sql and data step taking lot of time while running

aanan1417 — Mon, 21 Nov 2022 07:32:35 GMT

Data Set NameSAS0416.CHDR_202210Observations28123576

Member Type

DATA

Variables

193

Engine

META

Indexes

Created

11/02/2022 19:05:51

Observation Length

1120

Last Modified

11/02/2022 19:05:51

Deleted Observations

Protection

Compressed

Data Set Type

Sorted

Label

Data Representation

WINDOWS_64

Encoding

wlatin1 Western (Windows)

Data Set Page Size90112

Engine/Host Dependent Information

Number of Data Set Pages351545First Data Page1Max Obs per Page80Obs in First Data Page60Number of Data Set Repairs0ExtendObsCounterYESFilenameG:\Reinsurancelib\chdr_202210.sas7bdatRelease Created9.0401M6Host CreatedX64_SRV16Owner NameRELIGARE\yadav.rahulFile Size30GBFile Size (bytes)31678513152

53ACCTCCYChar3$3.$3.ACCTCCY

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat	Label

56ACCTMETHChar1$1.$1.ACCTMETH50AGNTCOYChar1$1.$1.AGNTCOY51AGNTNUMChar8$8.$8.AGNTNUM49AGNTPFXChar2$2.$2.AGNTPFX23ANNAMT01Num819.219.2ANNAMT0124ANNAMT02Num819.219.2ANNAMT0225ANNAMT03Num819.219.2ANNAMT0326ANNAMT04Num819.219.2ANNAMT0427ANNAMT05Num819.219.2ANNAMT0528ANNAMT06Num819.219.2ANNAMT06125APLSPFROMNum89.9.APLSPFROM132APLSPTONum89.9.APLSPTO118APLSUPRChar1$1.$1.APLSUPR46ASGNCOYChar1$1.$1.ASGNCOY47ASGNNUMChar8$8.$8.ASGNNUM45ASGNPFXChar2$2.$2.ASGNPFX111BANKACCKEYChar20$20.$20.BANKACCKEY184BANKCODEChar2$2.$2.BANKCODE110BANKKEYChar10$10.$10.BANKKEY174BFCHARGEChar1$1.$1.BFCHARGE105BILLAMT01Num819.219.2BILLAMT01106BILLAMT02Num819.219.2BILLAMT02107BILLAMT03Num819.219.2BILLAMT03108BILLAMT04Num819.219.2BILLAMT0463BILLCDNum89.9.BILLCD58BILLCHNLChar2$2.$2.BILLCHNL101BILLDATE01Num89.9.BILLDATE01102BILLDATE02Num89.9.BILLDATE02103BILLDATE03Num89.9.BILLDATE03104BILLDATE04Num89.9.BILLDATE0461BILLDAYChar2$2.$2.BILLDAY57BILLFREQChar2$2.$2.BILLFREQ62BILLMONTHChar2$2.$2.BILLMONTH126BILLSPFROMNum89.9.BILLSPFROM133BILLSPTONum89.9.BILLSPTO119BILLSUPRChar1$1.$1.BILLSUPR64BTDATENum89.9.BTDATE139CAMPAIGNChar6$6.$6.CAMPAIGN21CCDATENum89.9.CCDATE2CHDRCOYChar1$1.$1.CHDRCOY3CHDRNUMChar8$8.$8.CHDRNUM1CHDRPFXChar2$2.$2.CHDRPFX141CHDRSTCDAChar3$3.$3.CHDRSTCDA142CHDRSTCDBChar3$3.$3.CHDRSTCDB143CHDRSTCDCChar3$3.$3.CHDRSTCDC144CHDRSTCDDChar3$3.$3.CHDRSTCDD145CHDRSTCDEChar3$3.$3.CHDRSTCDE169CHGFLAGChar1$1.$1.CHGFLAG158CLUCOYChar1$1.$1.CLUCOY159CLUNUMChar8$8.$8.CLUNUM157CLUPFXChar2$2.$2.CLUPFX48CNTBRANCHChar2$2.$2.CNTBRANCH52CNTCURRChar3$3.$3.CNTCURR181CNTISSNum89.9.CNTISS182CNTRCVNum89.9.CNTRCV6CNTTYPEChar3$3.$3.CNTTYPE59COLLCHNLChar2$2.$2.COLLCHNL127COMMSPFROMNum89.9.COMMSPFROM134COMMSPTONum89.9.COMMSPTO120COMMSUPRChar1$1.$1.COMMSUPR190COPPNNum813.813.8COPPN189COTYPEChar1$1.$1.COTYPE180COVERNTChar8$8.$8.COVERNT37COWNCOYChar1$1.$1.COWNCOY38COWNNUMChar8$8.$8.COWNNUM36COWNPFXChar2$2.$2.COWNPFX54CRATENum820.920.9CRATE22CRDATENum89.9.CRDATE10CURRFROMNum89.9.CURRFROM11CURRTONum89.9.CURRTO193DATIMENum8DATETIME25.6DATETIME25.6DATIME43DESPCOYChar1$1.$1.DESPCOY44DESPNUMChar8$8.$8.DESPNUM42DESPPFXChar2$2.$2.DESPPFX112DISCODE01Char1$1.$1.DISCODE01113DISCODE02Char1$1.$1.DISCODE02114DISCODE03Char1$1.$1.DISCODE03115DISCODE04Char1$1.$1.DISCODE04175DISHNRCNTNum83.3.DISHNRCNT177DISHNRDTENum89.9.DISHNRDTE185DOCNUMChar8$8.$8.DOCNUM188DTECANNum89.9.DTECAN166DUEFLGChar1$1.$1.DUEFLG109FACTHOUSChar2$2.$2.FACTHOUS161FINCOYChar1$1.$1.FINCOY162FINNUMChar8$8.$8.FINNUM160FINPFXChar2$2.$2.FINPFX116GRUPKEYChar12$12.$12.GRUPKEY77INSTBCHNLChar2$2.$2.INSTBCHNL78INSTCCHNLChar2$2.$2.INSTCCHNL79INSTFREQChar2$2.$2.INSTFREQ75INSTFROMNum89.9.INSTFROM98INSTJCTLChar24$24.$24.INSTJCTL92INSTPAST01Num819.219.2INSTPAST0193INSTPAST02Num819.219.2INSTPAST0294INSTPAST03Num819.219.2INSTPAST0395INSTPAST04Num819.219.2INSTPAST0496INSTPAST05Num819.219.2INSTPAST0597INSTPAST06Num819.219.2INSTPAST0686INSTSTAMT01Num819.219.2INSTSTAMT0187INSTSTAMT02Num819.219.2INSTSTAMT0288INSTSTAMT03Num819.219.2INSTSTAMT0389INSTSTAMT04Num819.219.2INSTSTAMT0490INSTSTAMT05Num819.219.2INSTSTAMT0591INSTSTAMT06Num819.219.2INSTSTAMT0676INSTTONum89.9.INSTTO80INSTTOT01Num819.219.2INSTTOT0181INSTTOT02Num819.219.2INSTTOT0282INSTTOT03Num819.219.2INSTTOT0383INSTTOT04Num819.219.2INSTTOT0484INSTTOT05Num819.219.2INSTTOT0585INSTTOT06Num819.219.2INSTTOT06147JACKETChar8$8.$8.JACKET192JOB_NAMEChar10$10.$10.JOB_NAME167LAPRINDChar1$1.$1.LAPRIND128LAPSSPFROMNum89.9.LAPSSPFROM135LAPSSPTONum89.9.LAPSSPTO121LAPSSUPRChar1$1.$1.LAPSSUPR129MAILSPFROMNum89.9.MAILSPFROM136MAILSPTONum89.9.MAILSPTO122MAILSUPRChar1$1.$1.MAILSUPR60MANDREFChar5$5.$5.MANDREF117MEMBSELChar10$10.$10.MEMBSEL155MPLCOYChar1$1.$1.MPLCOY156MPLNUMChar8$8.$8.MPLNUM154MPLPFXChar2$2.$2.MPLPFX99NOFOUTINSTNum84.4.NOFOUTINST146NOFRISKSNum85.5.NOFRISKS130NOTSSPFROMNum89.9.NOTSSPFROM137NOTSSPTONum89.9.NOTSSPTO123NOTSSUPRChar1$1.$1.NOTSSUPR20OCCDATENum89.9.OCCDATE100OUTSTAMTNum819.219.2OUTSTAMT66PAYFLAGChar1$1.$1.PAYFLAG55PAYPLANChar6$6.$6.PAYPLAN40PAYRCOYChar1$1.$1.PAYRCOY41PAYRNUMChar8$8.$8.PAYRNUM39PAYRPFXChar2$2.$2.PAYRPFX152PDINDChar2$2.$2.PDIND176PDTYPEChar1$1.$1.PDTYPE164POACOYChar1$1.$1.POACOY165POANUMChar8$8.$8.POANUM163POAPFXChar2$2.$2.POAPFX170POLPLNChar10$10.$10.POLPLN13PROCFLAGChar2$2.$2.PROCFLAG14PROCIDChar14$14.$14.PROCID12PROCTRANCDChar4$4.$4.PROCTRANCD148PSTATCODEChar2$2.$2.PSTATCODE150PSTATDATENum89.9.PSTATDATE149PSTATREASNChar2$2.$2.PSTATREASN151PSTATTRANNum86.6.PSTATTRAN65PTDATENum89.9.PTDATE183QUOTENOChar8$8.$8.QUOTENO4RECODEChar2$2.$2.RECODE153REGISTERChar3$3.$3.REGISTER35REPNUMChar25$25.$25.REPNUM34REPTYPEChar2$2.$2.REPTYPE32RNLATTNChar2$2.$2.RNLATTN33RNLDURNNum83.3.RNLDURN30RNLNOTSChar2$2.$2.RNLNOTS31RNLNOTTOChar2$2.$2.RNLNOTTO186RNLSTSChar2$2.$2.RNLSTS29RNLTYPEChar2$2.$2.RNLTYPE131RNWLSPFROMNum89.9.RNWLSPFROM138RNWLSPTONum89.9.RNWLSPTO124RNWLSUPRChar1$1.$1.RNWLSUPR5SERVUNITChar2$2.$2.SERVUNIT69SINSTAMT01Num819.219.2SINSTAMT0170SINSTAMT02Num819.219.2SINSTAMT0271SINSTAMT03Num819.219.2SINSTAMT0372SINSTAMT04Num819.219.2SINSTAMT0473SINSTAMT05Num819.219.2SINSTAMT0574SINSTAMT06Num819.219.2SINSTAMT0667SINSTFROMNum89.9.SINSTFROM68SINSTTONum89.9.SINSTTO168SPECINDChar1$1.$1.SPECIND140SRCEBUSChar2$2.$2.SRCEBUS15STATCODEChar2$2.$2.STATCODE17STATDATENum89.9.STATDATE16STATREASNChar2$2.$2.STATREASN18STATTRANNum86.6.STATTRAN178STMPDTYAMTNum817.217.2STMPDTYAMT179STMPDTYDTENum89.9.STMPDTYDTE187SUSTRCDEChar4$4.$4.SUSTRCDE8TRANIDChar14$14.$14.TRANID19TRANLUSEDNum86.6.TRANLUSED7TRANNONum86.6.TRANNO191USER_PROFILEChar10$10.$10.USER_PROFILE9VALIDFLAGChar1$1.$1.VALIDFLAG171WVFDATNum89.9.WVFDAT173WVFINDChar1$1.$1.WVFIND172WVTDATNum89.9.WVTDAT

Re: sql and data step taking lot of time while running

andreas_lds — Mon, 21 Nov 2022 07:50:11 GMT

The important information is:

File Size30GBFile Size (bytes)31678513152

Copying 30 GB will take some time, but should not take hours. Have you tried proc copy?

Re: sql and data step taking lot of time while running

aanan1417 — Mon, 21 Nov 2022 07:52:42 GMT

i will try proc copy.

Re: sql and data step taking lot of time while running

Kurt_Bremser — Mon, 21 Nov 2022 07:54:10 GMT

If G: is a network share, I'm not surprised.

Run your steps with

options fullstimer;

set, so we can see where the time is lost.

Re: sql and data step taking lot of time while running

aanan1417 — Mon, 21 Nov 2022 09:40:28 GMT

Could you please share the code

Re: sql and data step taking lot of time while running

Kurt_Bremser — Mon, 21 Nov 2022 10:30:00 GMT

COPY Procedure

Re: sql and data step taking lot of time while running

LinusH — Mon, 21 Nov 2022 10:46:03 GMT

Having data partitioned in 30 GB datasets doesn't seem like a good architecture to me. You should probably look to other option better equipped with this type of data, better suited for data management and query. Within SAS SPDE is slightly better (at least for query).

Also, why do you need to make copies of data sets? Why not use them where they resides?

193 variables sounds like that you probably have quite a lot of redundancy. To make the data set smaller you could try compression or normalizing the data structure.

Re: sql and data step taking lot of time while running

Astounding — Mon, 21 Nov 2022 12:34:21 GMT

Another way to eliminate this copy step would be to modify the earlier step that creates test1.CHDR. Why not have that earlier step create sas0416.CHDR_202210 instead?

topic Re: sql and data step taking lot of time while running in New SAS User

sql and data step taking lot of time while running

Re: sql and data step taking lot of time while running

Re: sql and data step taking lot of time while running

Re: sql and data step taking lot of time while running

Re: sql and data step taking lot of time while running

Re: sql and data step taking lot of time while running

Re: sql and data step taking lot of time while running

Re: sql and data step taking lot of time while running

Re: sql and data step taking lot of time while running

Re: sql and data step taking lot of time while running